﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ebay.Model;
using ebay.Utility;

namespace ebay.Dal
{
  public class Orders
  {
    /// <summary>
    /// 产品替换
    /// </summary>
    /// <param name="newQuantity">替换数量</param>
    /// <param name="oldQuantity">员数量</param>
    /// <param name="oid">订单号集合</param>
    /// <param name="oldProductId">原产品代号</param>
    /// <param name="NewProductId">新产品代号</param>
    /// <param name="userId">操作人</param>
    /// <param name="Msg">操作讯息</param>
    /// <returns>执行结果</returns>
    public bool ProductReplace(decimal newQuantity, decimal oldQuantity, string[] oid, string oldProductId, string NewProductId, string userId, ref string Msg)
    {
      string sqlWhere = " Where ";
      for (int i = 0; i < oid.Length; i++)
      {
        sqlWhere += "(EbayUserId = '" + oid[i] + "' And ProductId = '" + oldProductId + "' And OrderLineItemId = '" + oid[i + 1] + "') Or";
        i++;
      }
      sqlWhere = sqlWhere.Remove(sqlWhere.Length - 3);
      string serId = DBUtility.GetSrialNumber(DateTime.Now, "Rep");
      if (string.IsNullOrEmpty(serId))
      {
        Msg = "流水号获取失败";
        return false;
      }
      string pcmdText = "Select Count(1) From Product Where ProductId = '" + NewProductId + "'";
      try
      {
        int result = (int)SqlHelper.ExecuteScalar(SqlHelper.ceterDB_Conn, CommandType.Text, pcmdText, null);
        if (result == 0)
        {
          Msg = "新商品代码不正确!";
          return false;
        }
      }
      catch
      {
        Msg = "服务器错误!";
        throw;
      }
      string cmdText = "Insert Into ProductReplace(SerialId,EbayUserId,OrderLineItemId,ProductOldId,ProductNewId,QuantityOld,QuantityNew,RepDate,RepUser)(Select '" + serId + "' as SerId,EbayUserId,OrderLineItemId,'" + oldProductId + "' as ProductOldId,'" + NewProductId + "' as ProductNewId,Quantity,ceiling(Quantity* " + newQuantity / oldQuantity + ") as NewQuantity,GetDate() As RepDate,'" + userId + "' From CustomerResolve " + sqlWhere + " )";
      string cmdText1 = "Update CustomerResolve Set mtime = getdate(), ProductId = '" + NewProductId + "', Quantity = Ceiling(Quantity*" + newQuantity / oldQuantity + ") " + sqlWhere;
      try
      {
        int val = 0;
        using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
        {
          connection.Open();
          using (SqlTransaction trans = connection.BeginTransaction())
          {
            val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, null);
            val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText1, null);
            try
            {
              trans.Commit();
            }
            catch
            {
              trans.Rollback();
              Msg = "服务器错误";
              return false;
            }
          }
        }
        if (val > 0)
        {
          return true;
        }

      }
      catch
      {
        Msg = "服务器错误";
        throw;
      }
      return false;
    }

    public bool OrderProductReplace(decimal newQuantity, decimal oldQuantity, string oldProductId, string NewProductId, string ebayUserId, string orderLineItemId, string userId, ref string Msg)
    {
      string sqlWhere = " Where ";
      sqlWhere += "EbayUserId = '" + ebayUserId + "' And ProductId = '" + oldProductId + "' And OrderLineItemId = '" + orderLineItemId + "'";

      string serId = DBUtility.GetSrialNumber(DateTime.Now, "Rep");
      if (string.IsNullOrEmpty(serId))
      {
        Msg = "流水号获取失败";
        return false;
      }
      string pcmdText = "Select Count(1) From Product Where ProductId = '" + NewProductId + "'";
      try
      {
        int result = (int)SqlHelper.ExecuteScalar(SqlHelper.ceterDB_Conn, CommandType.Text, pcmdText, null);
        if (result == 0)
        {
          Msg = "新商品代码不正确!";
          return false;
        }
      }
      catch
      {
        Msg = "服务器错误!";
        throw;
      }
      string cmdText = "Insert Into ProductReplace(SerialId,EbayUserId,OrderLineItemId,ProductOldId,ProductNewId,QuantityOld,QuantityNew,RepDate,RepUser)(Select '" + serId + "' as SerId,EbayUserId,OrderLineItemId,'" + oldProductId + "' as ProductOldId,'" + NewProductId + "' as ProductNewId,Quantity,ceiling(Quantity* " + newQuantity / oldQuantity + ") as NewQuantity,GetDate() As RepDate,'" + userId + "' From CustomerResolve " + sqlWhere + " )";
      string cmdText1 = "Update CustomerResolve Set mtime = getdate() ProductId = '" + NewProductId + "', Quantity = Ceiling(Quantity*" + newQuantity / oldQuantity + ") " + sqlWhere;
      try
      {
        int val = 0;
        using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
        {
          connection.Open();
          using (SqlTransaction trans = connection.BeginTransaction())
          {
            val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, null);
            val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText1, null);
            try
            {
              trans.Commit();
            }
            catch
            {
              trans.Rollback();
              Msg = "服务器错误";
              return false;
            }
          }
        }
        if (val > 0)
        {
          return true;
        }

      }
      catch
      {
        Msg = "服务器错误";
        throw;
      }
      return false;
    }

    /// <summary>
    /// 订单匹配
    /// </summary>
    /// <param name="dt">匹配日期</param>
    /// <param name="Muser">操作人员</param>
    /// <param name="ebayUserId">Ebay帐号</param>
    /// <param name="Msg">执行讯息</param>
    public void MatchOrders(DateTime dt, string Muser, string ebayUserId, ref string Msg)//修改时间：2012-9-4
    {
      SqlParameter[] p = { 
        new SqlParameter("@OrderDate",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@CompleteCount",SqlDbType.Int),
        new SqlParameter("@splitlist",SqlDbType.Int)
      };
      p[0].Value = dt.ToString("yyyyMMdd");
      p[1].Value = ebayUserId;
      p[2].Value = Muser;
      p[3].Direction = ParameterDirection.Output;
      p[4].Direction = ParameterDirection.Output;
      //解析
      //SqlParameter[] p2 = { 
      //                   new SqlParameter("@OrderDate",SqlDbType.VarChar),
      //                   new SqlParameter("@EbayUserId",SqlDbType.VarChar),
      //                   new SqlParameter("@Muser",SqlDbType.VarChar),                             
      //                   new SqlParameter("@splitlist",SqlDbType.Int)
      //                   };
      //p2[0].Value = dt.ToString("yyyyMMdd");
      //p2[1].Value = ebayUserId;
      //p2[2].Value = Muser;
      //p2[3].Direction = ParameterDirection.Output;

      SqlParameter[] p3 = { 
        new SqlParameter("@OrderDate",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)                              
      };
      p3[0].Value = dt.ToString("yyyyMMdd");
      p3[1].Value = ebayUserId;

      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_MatchOrder1", 120, p);
            //SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Match_Part1", 60, p2);
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Match_Part3", p3);

            trans.Commit();
            Msg += "<div>帐号：【" + ebayUserId + "】成功匹配【" + p[3].Value + "】条,解析商品【" + p[4].Value + "】个。</div>";
          }
          catch (Exception ex)
          {
            Msg += "<div>帐号：【" + ebayUserId + "】匹配失败。原因【" + ex.Message + "】。</div>";
            trans.Rollback();
          }
        }
      }
    }

    //匹配过程中的提示信息。
    public DataTable MatchCheckStatus(string ID)
    {
      //string cmdText = @"Select MTIME,OUTPUT From MatchOrder_Background where ID = @ID AND STATUS ='START' ORDER BY MTIME DESC"; 
      //string cmdText = @"Select OUTPUT,MESSAGE  FROM VIEW_MATCHORDER WHERE ID IN (@ID)";      
      string sqlWhere = @"";
      string[] tempid = ID.Split(',');
      for (int i = 0; i < tempid.Length; i++)
      {
        sqlWhere += " (ID = '" + tempid[i] + "') Or";
      }
      sqlWhere = sqlWhere.Remove(sqlWhere.Length - 3);
      string cmdText = @"Select OUTPUT,MESSAGE  FROM VIEW_MATCHORDER WHERE " + sqlWhere;
      DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, null);
      return table;
    }
    /// <summary>
    /// 订单匹配
    /// </summary>
    /// <param name="dt">匹配日期</param>
    /// <param name="Muser">操作人员</param>
    /// <param name="ebayUserId">Ebay帐号</param>
    /// <param name="Msg">执行讯息</param>
    /// 
    //private Guid iguid_matchorder = Guid.NewGuid();
    //public void MatchOrders_Symphony( string ID,DateTime dt, string Muser, string ebayUserId, ref string Msg)//修改时间：2012-9-4
    //{
    //    SqlParameter[] p = { 
    //                       //new SqlParameter("@ID",SqlDbType.UniqueIdentifier),  
    //                       new SqlParameter("@ID",SqlDbType.VarChar), 
    //                       new SqlParameter("@OrderDate",SqlDbType.VarChar),
    //                       new SqlParameter("@EbayUserId",SqlDbType.VarChar),
    //                       new SqlParameter("@Muser",SqlDbType.VarChar)
    //                       };            
    //    p[0].Value = ID;
    //    p[1].Value = dt.ToString("yyyyMMdd");
    //    p[2].Value = ebayUserId;
    //    p[3].Value = Muser;

    //    using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
    //    {
    //        connection.Open();
    //        using (SqlTransaction trans = connection.BeginTransaction())
    //        {
    //            try
    //            {
    //                SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_MatchOrder_Background", 120, p);

    //                trans.Commit();
    //            }
    //            catch (Exception ex)
    //            {
    //                trans.Rollback();
    //            }
    //        }
    //    }
    //}
    ////当前不需要
    //public void MatchOrders_Symphony(string ID,DateTime dt, string Muser, ref string Msg)
    //{
    //    DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, "Select EbayUserId From EbayAccount", null);

    //    foreach (DataRow r in table.Rows)
    //    {               
    //        string ebayUserId = r[0].ToString();
    //        MatchOrders_Symphony(ID,dt, Muser, ebayUserId, ref Msg);
    //    }
    //}
    //public void MatchOrders(DateTime dt, string Muser, string ebayUserId, ref string Msg)
    //{
    //    SqlParameter[] p = { 
    //                       new SqlParameter("@OrderDate",SqlDbType.VarChar),
    //                       new SqlParameter("@EbayUserId",SqlDbType.VarChar),
    //                       new SqlParameter("@Muser",SqlDbType.VarChar),
    //                       new SqlParameter("@CompleteCount",SqlDbType.Int),
    //                       new SqlParameter("@splitlist",SqlDbType.Int)
    //                       };
    //    p[0].Value = dt.ToString("yyyyMMdd");
    //    p[1].Value = ebayUserId;
    //    p[2].Value = Muser;
    //    p[3].Direction = ParameterDirection.Output;
    //    p[4].Direction = ParameterDirection.Output;
    //    using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
    //    {
    //        connection.Open();
    //        using (SqlTransaction trans = connection.BeginTransaction())
    //        {
    //            try
    //            {
    //                SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_MatchOrder", 100, p);
    //                trans.Commit();
    //                Msg += "<div>帐号：【" + ebayUserId + "】成功匹配【" + p[3].Value + "】条,解析商品【" + p[4].Value + "】个。</div>";

    //            }
    //            catch (Exception ex)
    //            {
    //                Msg += "<div>帐号：【" + ebayUserId + "】匹配失败。原因【" + ex.Message + "】。</div>";
    //                trans.Rollback();
    //            }
    //        }
    //    }
    //}

    //修改时间：2012-10-08
    public void MatchOrders_Symphony(string ID, DateTime begindt, DateTime enddt, string Muser, string ebayUserId, ref string Msg)//修改时间：2012-9-4
    {
      SqlParameter[] p = { 
        //new SqlParameter("@ID",SqlDbType.UniqueIdentifier),  
        new SqlParameter("@ID",SqlDbType.VarChar), 
        new SqlParameter("@OrderBeginDate",SqlDbType.VarChar),
        new SqlParameter("@OrderEndDate",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar)
      };
      p[0].Value = ID;
      p[1].Value = begindt.ToString("yyyyMMdd");
      p[2].Value = enddt.ToString("yyyyMMdd");
      p[3].Value = ebayUserId;
      p[4].Value = Muser;

      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_MatchOrder_Background", 120, p);

            trans.Commit();
          }
          catch (Exception)
          {
            trans.Rollback();
          }
        }
      }
    }
    //修改时间：2012-10-08
    public void MatchOrders_Symphony(string ID, DateTime begindt, DateTime enddt, string Muser, ref string Msg)
    {
      DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, "Select EbayUserId From EbayAccount", null);

      foreach (DataRow r in table.Rows)
      {
        string ebayUserId = r[0].ToString();
        MatchOrders_Symphony(ID, begindt, enddt, Muser, ebayUserId, ref Msg);
      }
    }
    /// <summary>
    /// 帐号匹配(所有帐号)
    /// </summary>
    /// <param name="dt">匹配日期</param>
    /// <param name="Muser">操作人员</param>
    /// <param name="Msg">操作讯息</param>
    public void MatchOrders(DateTime dt, string Muser, ref string Msg)
    {
      DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, "Select EbayUserId From EbayAccount", null);

      foreach (DataRow r in table.Rows)
      {
        string ebayUserId = r[0].ToString();
        MatchOrders(dt, Muser, ebayUserId, ref Msg);
      }
    }

    /// <summary>
    /// 确认收款
    /// </summary>
    /// <param name="Where">查询条件</param>
    /// <param name="userId">操作人员</param>
    /// <param name="list">查询参数集合</param>
    /// <returns>操作结果</returns>
    public bool ConfirmPay(string Where, string userId, List<SqlParameter> list)
    {

      string cmdText = "Update Orders Set IsPay = 'Y',Muser = @Muser,Mtime = GetDate() Where IsPay = 'N' {0}";
      SqlParameter p = new SqlParameter("@Muser", SqlDbType.VarChar);
      p.Value = userId;
      list.Add(p);
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          SqlHelper.ExecuteNonQuery(trans, CommandType.Text, string.Format(cmdText, Where), list.ToArray());
          try
          {
            trans.Commit();
            return true;
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
      return false;
    }

    /// <summary>
    /// 合并订单
    /// </summary>
    /// <param name="list">要合并的订单号集合</param>
    /// <param name="userId">操作人员</param>
    /// <param name="Msg">操作讯息</param>
    /// <returns>操作结果</returns>
    public bool MegreOrders(List<OrderInfo> list, string userId, ref string Msg)
    {
      string cmdText = @"Insert Into Orders(EbayUserId, OrderLineItemId, OrderId, NewOrderId, OrderSalesRecordNumber, SalesRecordNumber, UserId, 
                        BuyerFullName, BuyerEmail, BuyerAddress1, BuyerAddress2, BuyerCity, BuyerCounty, BuyerState, BuyerPostcode, 
                        BuyerCountry, BuyerPhoneNumber, ItemId, TransactionID, ItemTitle, Quantity, SalePrice, FinalValueFee, 
                        PostageAndPackaging, Insurance, TotalPrice, PaymentMethod, PayPalTransactionID, SaleDate, CheckoutDate, 
                        PaidonDate, DispatchDate, PostageService, FeedbackLeft, FeedbackReceived, NotestoYourself, CustomLable, 
                        PrivateNotes, VariationSKU, Variation_Name, Variation_Value, VariationTitle, VariationViewItemURL, 
                        EBayPaymentStatus, FeedbackScore, PositiveFeedbackPercent, FeeOrCreditAmount, PaymentOrRefundAmount, 
                        Platform, CompleteStatus, CheckoutStatus, LastTimeModified, OrderStatusId, OrderRetentionReson, IsPay, 
                        FeedBackStatusId, ShippingStatusId, Standard, IsOutStock, DeliveryId, WarehouseId, Claim, PayPalFee, BuyerId, 
                        BuyerName, AddressLine1, AddressLine2, City, County, PostalCode, Country, ContactPhoneNumber, CancelUser, 
                        CancelDate, DeliveryUser, DeliveryDate, PDate, PrintUserId, PrintDate, IsReBuilderOrder, MatchStatusId, MatchDate, 
                        Memo, Mnote, Muser, Mtime, Auser, Atime,ReBuilderReson,SendQuantity
                )
                (
                SELECT   EbayUserId, OrderLineItemId + '_' + rid as OrderLineItemId, @NewOrderId As OrderId,'' as NewOrderId, @NewOrderId as OrderSalesRecordNumber, SalesRecordNumber + '_' + rid as SalesRecordNumber, UserId, 
                                BuyerFullName, BuyerEmail, BuyerAddress1, BuyerAddress2, BuyerCity, BuyerCounty, BuyerState, BuyerPostcode, 
                                BuyerCountry, BuyerPhoneNumber, ItemId, TransactionID, ItemTitle, Quantity, SalePrice, FinalValueFee, 
                                PostageAndPackaging, Insurance, TotalPrice, PaymentMethod, PayPalTransactionID, SaleDate, CheckoutDate, 
                                PaidonDate, DispatchDate, PostageService, FeedbackLeft, FeedbackReceived, NotestoYourself, CustomLable, 
                                PrivateNotes, VariationSKU, Variation_Name, Variation_Value, VariationTitle, VariationViewItemURL, 
                                EBayPaymentStatus, FeedbackScore, PositiveFeedbackPercent, FeeOrCreditAmount, PaymentOrRefundAmount, 
                                Platform, CompleteStatus, CheckoutStatus, LastTimeModified, OrderStatusId, OrderRetentionReson, IsPay, 
                                FeedBackStatusId, ShippingStatusId, Standard, IsOutStock, DeliveryId, WarehouseId, Claim, PayPalFee, BuyerId, 
                                BuyerName, AddressLine1, AddressLine2, City, County, PostalCode, Country, ContactPhoneNumber, CancelUser, 
                                CancelDate, DeliveryUser, DeliveryDate, PDate, PrintUserId, PrintDate, IsReBuilderOrder, MatchStatusId, MatchDate, 
                                Memo, Mnote, @Muser, Mtime, Auser, Atime,ReBuilderReson,SendQuantity
                FROM 
                (
                        SELECT   EbayUserId, OrderLineItemId, OrderId,cast(Row_Number() over(Order By OrderLineItemId desc) as varchar) as rid ,NewOrderId, OrderSalesRecordNumber, SalesRecordNumber, UserId, 
                        BuyerFullName, BuyerEmail, BuyerAddress1, BuyerAddress2, BuyerCity, BuyerCounty, BuyerState, BuyerPostcode, 
                        BuyerCountry, BuyerPhoneNumber, ItemId, TransactionID, ItemTitle, Quantity, SalePrice, FinalValueFee, 
                        PostageAndPackaging, Insurance, TotalPrice, PaymentMethod, PayPalTransactionID, SaleDate, CheckoutDate, 
                        PaidonDate, DispatchDate, PostageService, FeedbackLeft, FeedbackReceived, NotestoYourself, CustomLable, 
                        PrivateNotes, VariationSKU, Variation_Name, Variation_Value, VariationTitle, VariationViewItemURL, 
                        EBayPaymentStatus, FeedbackScore, PositiveFeedbackPercent, FeeOrCreditAmount, PaymentOrRefundAmount, 
                        Platform, CompleteStatus, CheckoutStatus, LastTimeModified, OrderStatusId, OrderRetentionReson, IsPay, 
                        FeedBackStatusId, ShippingStatusId, Standard, IsOutStock, DeliveryId, WarehouseId, Claim, PayPalFee, BuyerId, 
                        BuyerName, AddressLine1, AddressLine2, City, County, PostalCode, Country, ContactPhoneNumber, CancelUser, 
                        CancelDate, DeliveryUser, DeliveryDate, PDate, PrintUserId, PrintDate, IsReBuilderOrder, MatchStatusId, MatchDate, 
                        Memo, Mnote, Muser, GetDate() as Mtime, Auser, Atime,ReBuilderReson,SendQuantity
                        FROM Orders Where EbayUserId = @EbayUserId And OrderId = @OrderId
                ) l
                )";
      //修改时间：2012-8-11 改动：添加缺少的字段SalePrice
      string cmdText1 = @"Insert Into CustomerResolve(EbayUserId,OrderLineItemId,ProductId,CustomerLable,Quantity,SalePrice,ItemCost,TransactionFees,PayFee,TheoryPostage,
                ActualPostage,PackageFee,RefundLoss,Rate,WarehouseId,Mnote,Muser,Mtime,Auser,Atime)
                (
                Select EbayUserId,OrderLineItemId + '_' + rid as OrderLineItemId,
                ProductId,CustomerLable,Quantity,SalePrice,ItemCost,
                TransactionFees,PayFee,TheoryPostage,
                ActualPostage,PackageFee,RefundLoss,Rate,WarehouseId,
                Mnote,@Muser,Mtime,Auser,Atime
                From 
                    (
                        Select CustomerResolve.EbayUserId,CustomerResolve.OrderLineItemId,
                        cast(Row_Number() Over(Order By CustomerResolve.OrderLineItemId) as varchar) as rid,
                        CustomerResolve.ProductId,CustomerResolve.CustomerLable,CustomerResolve.Quantity,CustomerResolve.SalePrice,CustomerResolve.ItemCost,
                        CustomerResolve.TransactionFees,CustomerResolve.PayFee,CustomerResolve.TheoryPostage,
                        CustomerResolve.ActualPostage,CustomerResolve.PackageFee,CustomerResolve.RefundLoss,CustomerResolve.Rate,CustomerResolve.WarehouseId,
                        CustomerResolve.Mnote,CustomerResolve.Muser,GetDate() as Mtime,CustomerResolve.Auser,CustomerResolve.Atime
                        From CustomerResolve
                        Left Outer join Orders On Orders.EbayUserId = CustomerResolve.EbayUserId And Orders.OrderLineItemId = CustomerResolve.OrderLineItemId
                        Where Orders.EbayUserId = @EbayUserId And Orders.OrderId = @OrderId
                    ) l
                )";
      string cmdText2 = "Update Orders Set OrderStatusId = 8 ,NewOrderId = @NewOrderId ,Muser = @Muser Where  Orders.EbayUserId = @EbayUserId And Orders.OrderId = @OrderId";
      SqlParameter[] p = { 
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@NewOrderId",SqlDbType.VarChar)
      };
      string NewOrderId = DBUtility.GetSrialNumber(DateTime.Now, "PUR");
      if (string.IsNullOrEmpty(NewOrderId))
      {
        Msg = "生成新订单号失败";
        return false;
      }
      try
      {
        using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
        {
          connection.Open();
          using (SqlTransaction trans = connection.BeginTransaction())
          {
            foreach (OrderInfo m in list)
            {
              p[0].Value = userId;
              p[1].Value = m.OrderId;
              p[2].Value = m.EbayUserId;
              p[3].Value = NewOrderId;
              SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
              SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText1, p);
              SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText2, p);
            }
            try
            {
              trans.Commit();
              return true;
            }
            catch
            {
              trans.Rollback();
            }
          }
        }
      }
      catch (SqlException e)
      {
        Msg = "服务器忙,请稍后再试";
        throw e;
      }
      catch (Exception ex)
      {
        Msg = "未知的错误";
        throw ex;

      }
      return false;
    }

    /// <summary>
    /// 退货单
    /// </summary>
    /// <param name="th_Id">退货单号</param>
    /// <param name="OrderPrice">订单金额</param>
    /// <param name="Currency">币别</param>
    /// <param name="CreateDate">单据日期</param>
    /// <param name="FitemId">职员编号</param>
    /// <param name="Quantity">商品Id</param>
    /// <param name="OrderType">0、发货单 1、退货单</param>
    /// <param name="statusId">0 正常发货 1 重新发货</param>
    /// <returns>数量</returns>
    public bool T_F_Order(int empId, string ebayUserId, string orderId, string Muser, int OrderType, int statusId, DateTime saleDate, DateTime PPCreateDate, List<ebay.Model.CustomerResolveContext> list, out string Msg)
    {
      string parcelId = "";
      if (OrderType == 1) //退货单
      {
        parcelId = DBUtility.GetSrialNumber(DateTime.Now, "THD");
      }
      else //发货单
      {
        parcelId = DBUtility.GetSrialNumber(DateTime.Now, "INV");
      }

      DateTime CreateDate = DateTime.Now;

      if (string.IsNullOrEmpty(parcelId))
      {
        Msg = "生成流水单号失败!";
        return false;
      }
      SqlParameter[] p = { 
        new SqlParameter("@BillNO",SqlDbType.VarChar),
        new SqlParameter("@AmountNoTax",SqlDbType.VarChar),
        new SqlParameter("@Currency",SqlDbType.VarChar),
        new SqlParameter("@FDate",SqlDbType.VarChar),
        new SqlParameter("@FEmpID",SqlDbType.Int),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@StatusId",SqlDbType.Int),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderType",SqlDbType.Int),
        new SqlParameter("@OrderCreateDate",SqlDbType.DateTime),
        new SqlParameter("@PaypalDate",SqlDbType.DateTime)
      };
      SqlParameter[] p2 = { 
        new SqlParameter("@BillNO",SqlDbType.VarChar),
        new SqlParameter("@AmountNoTax",SqlDbType.VarChar),
        new SqlParameter("@Currency",SqlDbType.VarChar),
        new SqlParameter("@FDate",SqlDbType.VarChar),
        new SqlParameter("@FEmpID",SqlDbType.Int),
        new SqlParameter("@FItemID",SqlDbType.Int),
        new SqlParameter("@FQty",SqlDbType.Decimal),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderType",SqlDbType.Int),
        new SqlParameter("@FStockID",SqlDbType.Int)
      };
      string cmdText = "";
      if (OrderType == 0)
      {
        cmdText = "Select count(1) From Orders Where OrderId = @OrderId and EbayUserId = @EbayUserId and OrderStatusId = 1 and MatchStatusId = 2 and IsPay = 'Y'";
      }
      else
      {
        cmdText = "Select count(1) From Orders Where OrderId = @OrderId and EbayUserId = @EbayUserId and OrderStatusId = 2";
      }

      SqlParameter[] p3 = { 
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      p3[0].Value = orderId;
      p3[1].Value = ebayUserId;
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          int val = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, cmdText, p3);
          if (val <= 0)
          {
            Msg = "订单:" + orderId + "不满足操作条件";
            return false;
          }
          decimal AmountMoney = 0m;
          string currency = "";
          try
          {
            foreach (ebay.Model.CustomerResolveContext m in list)
            {
              p2[0].Value = parcelId;
              p2[1].Value = m.SalePrice;
              p2[2].Value = m.Currency;
              currency = m.Currency;
              p2[3].Value = CreateDate;
              p2[4].Value = empId;
              p2[5].Value = m.FItemId;
              p2[6].Value = m.Quantity;
              p2[7].Value = ebayUserId;
              p2[8].Value = OrderType;
              p2[9].Value = m.FstockId;
              AmountMoney += (decimal)(m.Quantity * m.SalePrice);
              SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Ins_Order_T_LS_RetailEntry", p2);//写入明细
            }

            p[0].Value = parcelId;
            p[1].Value = AmountMoney;
            p[2].Value = currency;
            p[3].Value = CreateDate;
            p[4].Value = empId;
            p[5].Value = ebayUserId;
            p[6].Value = orderId;
            p[7].Value = statusId;
            p[8].Value = Muser;
            p[9].Value = OrderType;
            p[10].Value = saleDate;
            p[11].Value = PPCreateDate;

            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Ins_Order_T_LS_Retail", p);
            try
            {
              trans.Commit();
              Msg = "";
              return true;
            }
            catch
            {
              trans.Rollback();
              Msg = "订单:" + orderId + "保存失败";
              return false;
            }
          }
          catch
          {
            throw;
          }
        }
      }

    }

#region 审核测试
    //审核测试
    public bool T_F_OrderTest(int empId, string ebayUserId, string orderId, decimal gross, string Muser, int OrderType, int statusId, DateTime saleDate, DateTime PPCreateDate, List<ebay.Model.CustomerResolveContext> list, out string Msg)
    {
      string parcelId = "";
      if (OrderType == 1) //退货单
      {
        parcelId = DBUtility.GetSrialNumber(DateTime.Now, "THD");
      }
      else //发货单
      {
        parcelId = DBUtility.GetSrialNumber(DateTime.Now, "INV");
      }

      DateTime CreateDate = DateTime.Now;

      if (string.IsNullOrEmpty(parcelId))
      {
        Msg = "生成流水单号失败!";
        return false;
      }
          
      SqlParameter[] p = { 
        new SqlParameter("@BillNO",SqlDbType.VarChar),
        new SqlParameter("@AmountNoTax",SqlDbType.VarChar),
        new SqlParameter("@Currency",SqlDbType.VarChar),
        new SqlParameter("@FDate",SqlDbType.VarChar),
        new SqlParameter("@FEmpID",SqlDbType.Int),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@StatusId",SqlDbType.Int),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderType",SqlDbType.Int),
        new SqlParameter("@OrderCreateDate",SqlDbType.DateTime),
        new SqlParameter("@PaypalDate",SqlDbType.DateTime)
      };
      SqlParameter[] p2 = { 
        new SqlParameter("@BillNO",SqlDbType.VarChar),
        new SqlParameter("@AmountNoTax",SqlDbType.VarChar),
        new SqlParameter("@Currency",SqlDbType.VarChar),
        new SqlParameter("@FDate",SqlDbType.VarChar),
        new SqlParameter("@FEmpID",SqlDbType.Int),
        new SqlParameter("@FItemID",SqlDbType.Int),
        new SqlParameter("@FQty",SqlDbType.Decimal),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderType",SqlDbType.Int),
        new SqlParameter("@FStockID",SqlDbType.Int)
      };
      string cmdText = "";
      if (OrderType == 0)
      {
        cmdText = "Select count(1) From Orders Where OrderId = @OrderId and EbayUserId = @EbayUserId and OrderStatusId = 1 and MatchStatusId = 2 and IsPay = 'Y'";
      }
      else
      {
        cmdText = "Select count(1) From Orders Where OrderId = @OrderId and EbayUserId = @EbayUserId and OrderStatusId = 2";
      }

      SqlParameter[] p3 = { 
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      p3[0].Value = orderId;
      p3[1].Value = ebayUserId;

      //发货临时表存储计算值。
      string cmdText4 = @"delete K3_Price where EbayUserId=@EbayUserId and OrderId=@OrderId and FItemId=@FItemId;
                                Insert Into K3_Price (EbayUserId,OrderId, FItemId,TotalPrice,Cost,SalePrice,Price,Is_ToK3,Currency,Is_TH)
                                    Values(@EbayUserId,@OrderId,@FItemId,@TotalPrice,@Cost,@SalePrice,@Price,'Y',@Currency,'N')";            
      SqlParameter[] p4 = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@FItemId",SqlDbType.Int),
        new SqlParameter("@TotalPrice",SqlDbType.Decimal),
        new SqlParameter("@Cost",SqlDbType.Decimal),
        new SqlParameter("@SalePrice",SqlDbType.Decimal),
        new SqlParameter("@Price",SqlDbType.Decimal),
        new SqlParameter("@Currency",SqlDbType.VarChar)
      };
      //退货修改临时表 Is_TH='Y'
      string cmdText5 = @"Update K3_Price set Is_TH='Y' where EbayUserId=@EbayUserId and OrderId =@OrderId and FItemId=@FItemId";

      SqlParameter[] p5 = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@FItemId",SqlDbType.Int)                               
      };
      decimal cost = 0m;//成本
      decimal salePrice = 0m;//售价
      decimal AmountMoney = 0m;//计算中间值。
      decimal price = 0m;//单价

      if (OrderType != 1)//发货计算
      {
        foreach (ebay.Model.CustomerResolveContext m in list)
        {
          AmountMoney += (decimal)(m.Quantity * m.SalePrice);
        }
      }
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          int val = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, cmdText, p3);
          if (val <= 0)
          {
            Msg = "订单:" + orderId + "不满足操作条件";
            return false;
          }                   
          string currency = "";
          try
          {
            foreach (ebay.Model.CustomerResolveContext m in list)
            {
              if (OrderType != 1) //发货
              {
                double temp = double.Parse((gross / AmountMoney).ToString());
                cost = decimal.Parse(m.SalePrice.ToString());
                price = decimal.Parse((temp * m.SalePrice).ToString());
                salePrice = price * m.Quantity;

                p4[0].Value = ebayUserId;
                p4[1].Value = orderId;
                p4[2].Value = m.FItemId;
                p4[3].Value = gross;
                p4[4].Value = cost;
                p4[5].Value = salePrice;
                p4[6].Value = price;
                p4[7].Value = m.Currency;
                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText4, p4); //填写K3_Price表
              }
              else
              {
                p5[0].Value = ebayUserId;
                p5[1].Value = orderId;
                p5[2].Value = m.FItemId;
                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText5, p5); //填写退货K3_Price表
              }
              //写K3零售单明细
              //p2[0].Value = parcelId;                           
              //if (OrderType != 1) //发货
              //{
              //    p2[1].Value = salePrice;//售价
              //}
              //else
              //{
              //    p2[1].Value =m.SalePrice;//售价
              //}
              //p2[2].Value = m.Currency;
              //currency = m.Currency;
              //p2[3].Value = CreateDate;
              //p2[4].Value = empId;
              //p2[5].Value = m.FItemId;
              //p2[6].Value = m.Quantity;
              //p2[7].Value = ebayUserId;
              //p2[8].Value = OrderType;
              //p2[9].Value = m.FstockId;
                           
              //SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Ins_Order_T_LS_RetailEntry", p2);//写入明细
            }

            p[0].Value = parcelId;                        
            p[1].Value = gross; //总价格
            p[2].Value = currency;
            p[3].Value = CreateDate;
            p[4].Value = empId;
            p[5].Value = ebayUserId;
            p[6].Value = orderId;
            p[7].Value = statusId;
            p[8].Value = Muser;
            p[9].Value = OrderType;
            p[10].Value = saleDate;
            p[11].Value = PPCreateDate;

            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Ins_Order_T_LS_Retail", p);
            try
            {
              trans.Commit();
              Msg = "";
              return true;
            }
            catch
            {
              trans.Rollback();
              Msg = "订单:" + orderId + "保存失败";
              return false;
            }
          }
          catch
          {
            throw;
          }
        }
      }

    }
       
    // 临时存储写零售单数据
    public int K3_Price(string orderId, string ebayUserId, decimal gross, List<ebay.Model.CustomerResolveContext> list)
    {
      string cmdText = @"Insert Into K3_Price (EbayUserId,OrderId, FItemId,TotalPrice,Cost,SalePrice,Price,Is_ToK3,Currency)
                                    Values(@EbayUserId,@OrderId,@FItemId,@TotalPrice,@Cost,@SalePrice,@Price,'Y',@Currency)";
      SqlParameter[] p = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@FItemId",SqlDbType.Int),
        new SqlParameter("@TotalPrice",SqlDbType.Decimal),
        new SqlParameter("@Cost",SqlDbType.Decimal),
        new SqlParameter("@SalePrice",SqlDbType.Decimal),
        new SqlParameter("@Price",SqlDbType.Decimal),
        new SqlParameter("@Currency",SqlDbType.VarChar)
      };
      decimal cost = 0m;//成本
      decimal salePrice = 0m;//售价
      decimal AmountMoney = 0m;//临时计算中间值。
      decimal price = 0m;//单价
      foreach (ebay.Model.CustomerResolveContext m in list)
      {                
        AmountMoney +=(decimal)(m.Quantity * m.SalePrice);
      }
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            int val = 0;
            foreach (ebay.Model.CustomerResolveContext m in list)
            {
              double temp = double.Parse((gross / AmountMoney).ToString());
              cost = decimal.Parse(m.SalePrice.ToString());
              price = decimal.Parse((temp * m.SalePrice).ToString());
              salePrice = price * m.Quantity;
              p[0].Value = ebayUserId;
              p[1].Value = orderId;
              p[2].Value = m.FItemId;
              p[3].Value = gross;
              p[4].Value = cost;
              p[5].Value = salePrice;
              p[6].Value = price;
              p[7].Value = m.Currency;
              val += SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);
            }
            if (val > 0)
            {
              trans.Commit();
              return 1;
            }
          }
          catch (Exception)
          {
            trans.Rollback();
            return 0;
          }
        }
      }                                    
      return 0;
    }
        
#endregion
    /// <summary>
    /// 订单作废
    /// </summary>
    /// <param name="list">要作废的订单号集合</param>
    /// <param name="muser">操作人员</param>
    /// <returns>操作结果</returns>
    public bool OrderZfei(List<OrderInfo> list, string muser)
    {
      SqlParameter[] p = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          foreach (OrderInfo m in list)
          {
            p[0].Value = m.EbayUserId;
            p[1].Value = m.OrderId;
            p[2].Value = muser;
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_OrdersZfei", p);
          }
          try
          {
            trans.Commit();
            return true;
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
      return false;
    }

    /// <summary>
    /// 取消订单保留
    /// </summary>
    /// <param name="list">要操作的订单集合</param>
    /// <param name="Muser">操作人员</param>
    /// <returns>操作结果</returns>
    public bool OrderBao_liu(List<OrderInfo> list, string Muser)
    {
      string cmdText = @"Update Orders Set OrderStatusId = 12,Muser = @Muser,OrderRetentionReson = '',Mtime = getdate()
  FROM (
    SELECT o.OrderId, o.EbayUserId
      FROM  (
        SELECT o.EbayUserId
            ,o.OrderId
            ,r.ProductId
            ,SUM(r.Quantity) AS Quantity
          FROM Orders o
            ,CustomerResolve r
          WHERE o.OrderId=@OrderId AND o.EbayUserId=@EbayUserId 
			AND o.OrderLineItemId=r.OrderLineItemId AND o.EbayUserId=r.EbayUserId
          GROUP BY o.EbayUserId,o.OrderId,r.ProductId
        ) o
        , v_wfh v
      WHERE o.ProductId = v.Productid       
      GROUP BY o.OrderID, o.EbayUserId
      HAVING MIN(FQty-sQty-o.quantity)>= 0
    ) orv
  WHERE orv.OrderId = Orders.OrderId AND orv.EbayUserId = Orders.EbayUserId";
      SqlParameter[] p = { 
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          foreach (OrderInfo m in list)
          {
            p[0].Value = Muser;
            p[1].Value = m.OrderId;
            p[2].Value = m.EbayUserId;
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
          }
          try
          {
            trans.Commit();
          }
          catch
          {
            trans.Rollback();
            return false;
          }
        }
      }

      return true;
    }

    public bool CheckStock(String muser, out String msg)
    {
      String cmdText = @"SELECT DISTINCT o.OrderId, o.EbayUserId
      INTO #tbOrders
	  FROM Orders o
		,CustomerResolve r
		,Product p
		,v_wfh v
	  WHERE o.OrderStatusId = 12 AND o.MatchStatusId = 2 AND r.ProductId = p.ProductId
		AND o.OrderLineItemId = r.OrderLineItemId AND o.EbayUserId = r.EbayUserId AND r.ProductId = v.ProductId
		AND p.DeliveryWarehouseId= 40017 AND v.fStockId='5559' AND v.fQty<v.sQty
UPDATE Orders SET OrderStatusId=11, OrderRetentionReson='保留缺货', Muser = @Muser
  FROM #tbOrders o
  WHERE o.EbayUserId=orders.EbayUserId
    AND o.OrderId = Orders.OrderId";
            
      SqlParameter[] p = { 
        new SqlParameter("@Muser",SqlDbType.VarChar)
      };
            
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          p[0].Value = muser;
          SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, 600, p);
                    
          try
          {
            trans.Commit();
          }
          catch(Exception ex)
          {
            msg = ex.Message;
            trans.Rollback();
            return false;
          }
        }
        connection.Close();
      }

      msg = String.Empty;
      return true;
    }

    /// <summary>
    /// 取消匹配
    /// </summary>
    /// <param name="list">要操作的订单号集合</param>
    /// <param name="Muser">操作人员</param>
    /// <returns>操作结果</returns>
    public bool CancelMatch(List<OrderInfo> list, string Muser)
    {
      //string cmdText = "Update Orders Set  mtime = getdate(),BuyerId = '',BuyerName = '',AddressLine1 = '',AddressLine2 = '',City = '',County = '',PostalCode = '',Country = '',ContactPhoneNumber = '',Muser = @Muser,MatchStatusId = 1, OrderStatusId = 4 Where EbayUserId = @EbayUserId And OrderId = @OrderId";
      string cmdText = "Delete From Orders Where OrderId = @OrderId And EbayUserId = @EbayUserId ";
      string cmdText1 = "Update EbaySelling Set SellingStatus = 1 From Orders Where Orders.OrderId = EbaySelling.OrderId And Orders.EbayUserId = EbaySelling.EbayUserId And Orders.EbayUserId = @EbayUserId And Orders.OrderId = @OrderId";
      string cmdText2 = "Update PPOrder Set StatusId = 1 From Orders Where Orders.PayPalTransactionId = PPOrder.TransactionId And Orders.EbayUserId = PPOrder.EbayUserId And Orders.EbayUserId = @EbayUserId And Orders.OrderId = @OrderId";
      string cmdText3 = "Delete From MatchMapping Where EbayUserId = @EbayUserId And OrderId = @OrderId";
      SqlParameter[] pd = { 
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      SqlParameter[] p = { 
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          foreach (OrderInfo m in list)
          {
            p[0].Value = Muser;
            p[1].Value = m.OrderId;
            p[2].Value = m.EbayUserId;
            pd[0].Value = m.OrderId;
            pd[1].Value = m.EbayUserId;                        
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText1, pd);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText2, pd);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText3, pd);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, pd);
          }
          try
          {
            trans.Commit();
            return true;
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
      return false;
    }

    /// <summary>
    /// 反审核
    /// </summary>
    /// <param name="list">要操作的订单号集合</param>
    /// <param name="Muser">操作人员</param>
    /// <returns>操作结果</returns>
    public bool f_shen_he(List<OrderInfo> list, string Muser)
    {
      string cmdText = "Update Orders Set mtime = getdate(),OrderStatusId = 1,Muser = @Muser Where EbayUserId = @EbayUserId And OrderId = @OrderId";
      SqlParameter[] p = { 
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          foreach (OrderInfo m in list)
          {
            p[0].Value = Muser;
            p[1].Value = m.OrderId;
            p[2].Value = m.EbayUserId;
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
          }
          try
          {
            trans.Commit();
            return true;
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
      return false;
    }

    //反审核
    public int f_shen_he(string ebayUserId, string orderId,string Muser) //反审核
    {
      string cmdText = "Update Orders Set mtime = getdate(),OrderStatusId = 1,Muser = @Muser Where EbayUserId = @EbayUserId And OrderId = @OrderId";
      SqlParameter[] p = { 
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      p[0].Value = Muser;
      p[1].Value = orderId;
      p[2].Value = ebayUserId;

      string cmdText2 = @" Delete K3_Price where EbayUserId=@EbayUserId and OrderId =@OrderId";//删除审核临时表中的数据。
      SqlParameter[] p2 = {
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      }; 
      p2[0].Value = orderId;
      p2[1].Value = ebayUserId;

      int val = 0;
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            val = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
            val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText2, p2);
            if (val > 0)
            {
              trans.Commit();
              return 1;
            }
          }
          catch
          {
            trans.Rollback();
            return 0;
          }
        }
      }        
      return 0;
    }
    /// <summary>
    /// 订单拆分
    /// </summary>
    /// <param name="list1">拆分集合1</param>
    /// <param name="list2">拆分集合2</param>
    /// <param name="ebayUserId">Ebay帐号</param>
    /// <param name="OrderId">订单号</param>
    /// <param name="Muser">操作人员</param>
    /// <returns>操作结果</returns>
    public bool SplitOrder(List<SplitOrder> list1, List<SplitOrder> list2, string ebayUserId, string OrderId, string Muser)
    {
      string cmdText1 = @"Insert Into Orders(EbayUserId,OrderLineItemId,OrderId,NewOrderId,OrderSalesRecordNumber,SalesRecordNumber
            ,UserId,BuyerFullName,BuyerEmail,BuyerAddress1,BuyerAddress2,BuyerCity
            ,BuyerCounty,BuyerState,BuyerPostcode,BuyerCountry,BuyerPhoneNumber,ItemId
            ,TransactionID,ItemTitle,Quantity,SalePrice,FinalValueFee,PostageAndPackaging
            ,Insurance,TotalPrice,PaymentMethod,PayPalTransactionID,SaleDate,CheckoutDate
            ,PaidonDate,DispatchDate,PostageService,FeedbackLeft,FeedbackReceived,NotestoYourself
            ,CustomLable,PrivateNotes,VariationSKU,Variation_Name,Variation_Value,VariationTitle
            ,VariationViewItemURL,EBayPaymentStatus,FeedbackScore,PositiveFeedbackPercent,FeeOrCreditAmount
            ,PaymentOrRefundAmount,Platform,CompleteStatus,CheckoutStatus,LastTimeModified,OrderStatusId
            ,OrderRetentionReson,IsPay,FeedBackStatusId,ShippingStatusId,Standard,IsOutStock,DeliveryId
            ,WarehouseId,Claim,PayPalFee,BuyerId,BuyerName,AddressLine1,AddressLine2,City
            ,County,PostalCode,Country,ContactPhoneNumber,CancelUser,CancelDate,DeliveryUser
            ,DeliveryDate,PDate,PrintUserId,PrintDate,IsReBuilderOrder,ReBuilderReson
            ,MatchStatusId,MatchDate,Memo,Mnote,Muser,Mtime,Auser,Atime,SendQuantity
            )
            (
            select distinct o.EbayUserId,o.OrderLineItemId + @SortId,o.OrderId + @SortId,
            o.OrderSalesRecordNumber as NewOrderId,o.OrderSalesRecordNumber + @SortId,o.SalesRecordNumber + @SortId
            ,o.UserId,o.BuyerFullName,o.BuyerEmail,o.BuyerAddress1,o.BuyerAddress2,o.BuyerCity
            ,o.BuyerCounty,o.BuyerState,o.BuyerPostcode,o.BuyerCountry,o.BuyerPhoneNumber,o.ItemId
            ,o.TransactionID,o.ItemTitle,o.Quantity,o.SalePrice,o.FinalValueFee,o.PostageAndPackaging
            ,o.Insurance,o.TotalPrice,o.PaymentMethod,o.PayPalTransactionID,o.SaleDate,o.CheckoutDate
            ,o.PaidonDate,o.DispatchDate,o.PostageService,o.FeedbackLeft,o.FeedbackReceived,o.NotestoYourself
            ,o.CustomLable,o.PrivateNotes,o.VariationSKU,o.Variation_Name,o.Variation_Value,o.VariationTitle
            ,o.VariationViewItemURL,o.EBayPaymentStatus,o.FeedbackScore,o.PositiveFeedbackPercent,o.FeeOrCreditAmount
            ,o.PaymentOrRefundAmount,o.Platform,o.CompleteStatus,o.CheckoutStatus,o.LastTimeModified,o.OrderStatusId
            ,o.OrderRetentionReson,o.IsPay,o.FeedBackStatusId,o.ShippingStatusId,o.Standard,o.IsOutStock,o.DeliveryId
            ,o.WarehouseId,o.Claim,o.PayPalFee,o.BuyerId,o.BuyerName,o.AddressLine1,o.AddressLine2,o.City
            ,o.County,o.PostalCode,o.Country,o.ContactPhoneNumber,o.CancelUser,o.CancelDate,o.DeliveryUser
            ,o.DeliveryDate,o.PDate,o.PrintUserId,o.PrintDate,o.IsReBuilderOrder,o.ReBuilderReson
            ,o.MatchStatusId,o.MatchDate,o.Memo,o.Mnote,@Muser,getdate() as Mtime,o.Auser,o.Atime,o.SendQuantity from CustomerResolve r
            inner join Orders o on r.EbayUserId = o.EbayUserId and r.OrderLineItemId = o.OrderLineItemId
            where r.EbayUserId = @EbayUserId and ({0})
            )";
      string cmdText2 = @"Insert Into CustomerResolve(EbayUserId,OrderLineItemId,ProductId,CustomerLable,Quantity,SalePrice,ItemCost,TransactionFees,PayFee,TheoryPostage,
                ActualPostage,PackageFee,RefundLoss,Rate,WarehouseId,Mnote,Muser,Mtime,Auser,Atime)
                (
                Select EbayUserId,OrderLineItemId + @SortId as OrderLineItemId,ProductId,CustomerLable,Quantity,SalePrice,ItemCost,TransactionFees,PayFee,TheoryPostage,
                    ActualPostage,PackageFee,RefundLoss,Rate,WarehouseId,Mnote,@Muser,getdate() as Mtime,Auser,Atime
                    From CustomerResolve Where EbayUserId = @EbayUserId And OrderLineItemId = @OrderLineItemId And ProductId = @ProductId
                )";
      string cmdText3 = "Update Orders Set OrderStatusId = 9,Muser = @Muser Where Orders.OrderId = @OrderId And Orders.EbayUserId = @EbayUserId ";

      string sort1 = "C1";
      string sort2 = "C2";
      string products1 = "";
      string products2 = "";
      foreach (SplitOrder po in list1)
      {
        products1 += "(o.OrderLineItemId = '" + po.OrderLineItemId + "' and r.ProductId = '" + po.ProductId + "') or";
      }
      products1 = products1.Remove(products1.Length - 3);

      foreach (SplitOrder po in list2)
      {
        products2 += "(o.OrderLineItemId = '" + po.OrderLineItemId + "' and r.ProductId = '" + po.ProductId + "') or";
      }
      products2 = products2.Remove(products2.Length - 3);

      SqlParameter[] p = { 
        new SqlParameter("@SortId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      SqlParameter[] p2 = { 
        new SqlParameter("@SortId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar), 
        new SqlParameter("@OrderLineItemId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@ProductId",SqlDbType.VarChar)
      };
      SqlParameter[] p3 = { 
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar)
      };
      p[0].Value = sort1;
      p[1].Value = Muser;
      p[2].Value = ebayUserId;

      p3[0].Value = Muser;
      p3[1].Value = OrderId;
      p3[2].Value = ebayUserId;

      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          SqlHelper.ExecuteNonQuery(trans, CommandType.Text, string.Format(cmdText1, products1), p);

          p[0].Value = sort2;
          p[1].Value = Muser;
          p[2].Value = ebayUserId;
          SqlHelper.ExecuteNonQuery(trans, CommandType.Text, string.Format(cmdText1, products2), p);
          SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText3, p3);
          foreach (SplitOrder s in list1)
          {
            p2[0].Value = sort1;
            p2[1].Value = Muser;
            p2[2].Value = s.OrderLineItemId;
            p2[3].Value = ebayUserId;
            p2[4].Value = s.ProductId;
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText2, p2);
          }
          foreach (SplitOrder s in list2)
          {
            p2[0].Value = sort2;
            p2[1].Value = Muser;
            p2[2].Value = s.OrderLineItemId;
            p2[3].Value = ebayUserId;
            p2[4].Value = s.ProductId;
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText2, p2);
          }
          try
          {
            trans.Commit();
            return true;
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
      return false;
    }

    /// <summary>
    /// 订单退款
    /// </summary>
    /// <param name="ebayUserId">Ebay帐号</param>
    /// <param name="OrderId">订单号</param>
    /// <param name="TotalPrice">总金额</param>
    /// <param name="refundType">退款类型</param>
    /// <param name="disputeType">投诉类型</param>
    /// <param name="departmentId">部门代号</param>
    /// <param name="userId">用户代码</param>
    /// <returns>操作结果</returns>
    public bool Order_Tkuan(string ebayUserId, string OrderId, decimal TotalPrice, int refundType, int disputeType, int departmentId, string userId, List<ebay.Model.CustomerResolve> resolves)
    {

      string cmdSelect = "Select top 1 Orders.UserId,Orders.BuyerFullName,isnull(PPOrder.Currency,'GBP') As Currency,Orders.PayPalTransactionId as TransactionId From Orders Left Outer Join PPOrder On Orders.EbayUserId = PPOrder.EbayUserId And Orders.PayPalTransactionId = PPOrder.TransactionId Where Orders.EbayUserId = @EbayUserId And Orders.OrderId = @OrderId";
      SqlParameter[] ps = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar)                                
      };
      ps[0].Value = ebayUserId;
      ps[1].Value = OrderId;

      DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdSelect, ps);
      if (table.Rows.Count == 0)
      {
        return false;
      }
      string buyerId = table.Rows[0][0].ToString();
      string buyerName = table.Rows[0][1].ToString();
      string Currency = table.Rows[0][2].ToString();
      string transactionId = table.Rows[0][3].ToString();

      string cmdText = @"Insert Into Refund(RefundId,EbayUserId,OrderId,TransactionId,RefundAmount,Currency,StatusId,RefundType,DisputeType,BlameDepartments,RefundDate,BuyerId,BuyerFullName,InvalidDate,Mnote,Muser,Mtime,Auser,Atime,AuditFlag)
               Values (@RefundId,@EbayUserId,@OrderId,@TransactionId,@TotalPrice,@Currency,0,@RefundType,@DisputeType,@BlameDepartments,@RefundDate ,@BuyerId,@BuyerFullName,'1900-1-1','',@UserId,@Mtime,'','1900-1-1',@AuditFlag)";

      string cmdText1 = "Update Orders Set OrderStatusId = 5,Muser = @Muser,Mtime = GetDate() Where OrderId = @OrderId And EbayUserId = @EbayUserId";

      string cmdText2 = "Select OrderStatusId From Orders Where OrderId = @OrderId And EbayUserId = @EbayUserId";
      SqlParameter[] p = { 
        new SqlParameter("@RefundId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@TransactionId",SqlDbType.VarChar),
        new SqlParameter("@TotalPrice",SqlDbType.Money),
        new SqlParameter("@Currency",SqlDbType.VarChar),
        new SqlParameter("@RefundType",SqlDbType.Int),
        new SqlParameter("@DisputeType",SqlDbType.Int),
        new SqlParameter("@BlameDepartments",SqlDbType.Int),
        new SqlParameter("@RefundDate",SqlDbType.DateTime),
        new SqlParameter("@BuyerId",SqlDbType.VarChar),
        new SqlParameter("@BuyerFullName",SqlDbType.VarChar),
        new SqlParameter("@UserId",SqlDbType.VarChar),
        new SqlParameter("@Mtime",SqlDbType.DateTime),
        new SqlParameter("@AuditFlag",SqlDbType.Int)
      };
      SqlParameter[] p2 = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar) 
      };
      SqlParameter[] p3 = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar)
      };
      DateTime d = DateTime.Now;
      string refundId = DBUtility.GetSrialNumber(DateTime.Now, "TKD");
      if (string.IsNullOrEmpty(refundId))
      {
        return false;
      }
      p[0].Value = refundId;
      p[1].Value = ebayUserId;
      p[2].Value = OrderId;
      p[3].Value = transactionId;
      p[4].Value = TotalPrice;
      p[5].Value = Currency;
      p[6].Value = refundType;
      p[7].Value = disputeType;
      p[8].Value = departmentId;
      p[9].Value = d;
      p[10].Value = buyerId;
      p[11].Value = buyerName;
      p[12].Value = userId;
      p[13].Value = d;


      p2[0].Value = ebayUserId;
      p2[1].Value = OrderId;
      p2[2].Value = userId;

      p3[0].Value = ebayUserId;
      p3[1].Value = OrderId;

      String sqlInsertDetail = "INSERT RefundDetail(RefundId, eBayUserId, OrderLineItemId, ProductId, Num) VALUES(@RefundId, @eBayUserId, @OrderLineItemId, @ProductId, @Num)";

      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            object objval = SqlHelper.ExecuteScalar(trans, CommandType.Text, cmdText2, p3);
            switch (objval.ToString())
            {
              case "2":
                p[14].Value = 2;
                break;
              default:
                p[14].Value = 1;
                break;
            }

            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText1, p2);

            foreach (ebay.Model.CustomerResolve resolve in resolves)
            {

              SqlParameter[] parameters = {
                new SqlParameter("@RefundId", SqlDbType.VarChar, 30),
                new SqlParameter("@eBayUserId", SqlDbType.VarChar, 80),
                new SqlParameter("@OrderLineItemId", SqlDbType.VarChar, 30),
                new SqlParameter("@ProductId", SqlDbType.VarChar, 30),
                new SqlParameter("@Num", SqlDbType.Int)
              };

              parameters[0].Value = refundId;
              parameters[1].Value = resolve.EbayUserId;
              parameters[2].Value = resolve.OrderLineItemId;
              parameters[3].Value = resolve.ProductId;
              parameters[4].Value = resolve.Quantity;

              SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsertDetail, parameters);
            }
            try
            {
              trans.Commit();
              return true;
            }
            catch
            {
              trans.Rollback();
              return false;
            }
          }
          catch
          {
            throw;
          }
        }
      }

    }

    /// <summary>
    /// 过期作废操作
    /// </summary>
    public void OrderGqZf()
    {
      //            string cmdText = @"Update Orders Set OrderStatusId = 4,Mnote = '过期作废', mtime = getdate() Where
      //            datediff
      //            (
      //	            day,
      //	            saledate,
      //	            getdate()
      //            ) 
      //            - 
      //            (
      //            select top 1 OrderExpiredDays from ebayaccount 
      //	            where orders.ebayuserid = ebayaccount.ebayuserid
      //            ) > 0 and orders.ispay = 'N' and (Orders.OrderStatusId = 1 Or Orders.OrderStatusId = 3)";
      string cmdText = @"	update [CenterDB].[dbo].[Orders]
	                            set OrderStatusId =4,Mnote = '过期作废',mtime = getdate(),[CancelDate]=getdate()
	                            where OrderStatusId =3
	                              and DATEDIFF (D,[MatchDate],GETDATE ())>60
	                              and MatchStatusId =2";
      SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, null);
    }

    /// <summary>
    /// 手动匹配——关联，获取PPOrders中“Gross”之和
    /// </summary>
    /// <param name="pids">订单号集合</param>
    /// <returns>总金额</returns>
    public decimal GetPgross(string[] pids)
    {
      decimal gross = 0;
      string sqlWhere = " ";
      for (int i = 0; i < pids.Length; i++)
      {
        sqlWhere += " p.Id = '" + pids[i].ToString() + "' Or";
      }
      sqlWhere = sqlWhere.Remove(sqlWhere.Length - 3);
      string text = @"select  SUM(Gross) as Pgross from PPOrder p where" + sqlWhere;
      DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, text, null);
      if (dt != null && dt.Rows.Count > 0)
      {
        gross = Convert.ToDecimal(dt.Rows[0][0].ToString());
      }
      return gross;
    }

    /// <summary>
    /// 手动匹配——关联
    /// </summary>
    /// <param name="ebayUserId">Ebay帐号</param>
    /// <param name="oid">订单号</param>
    /// <param name="pgross">金额</param>
    /// <param name="pids"></param>
    /// <param name="userId"></param>
    public void MatchRelation(string ebayUserId, string oid, string transactionId, string userId)
    {
      string cmdText = "select top 1 MatchStatusId from Orders where OrderId = @OrderId and EbayUserId = @EbayUserId";

      string cmdText2 = "select top 1 StatusId from PPOrder where TransactionId = @TransactionId";//EbayUserId = @EbayUserId and

      SqlParameter[] p0 = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),                    

           
        new SqlParameter("@OrderId",SqlDbType.VarChar)    
      };
      SqlParameter[] p2 = { 
        // new SqlParameter("@EbayUserId",SqlDbType.VarChar),                    

        new SqlParameter("@TransactionId",SqlDbType.VarChar)    
      };
      p0[0].Value = ebayUserId;
      p0[1].Value = oid;
      //p2[0].Value = ebayUserId;
      p2[0].Value = transactionId;

      object val1 = SqlHelper.ExecuteScalar(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p0);
      object val2 = SqlHelper.ExecuteScalar(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText2, p2);

      if (val1 == DBNull.Value)
      {
        throw new Exception("订单号不存在");
      }
      if (val2 == DBNull.Value)
      {
        throw new Exception("交易号不存在");
      }
      string sVal1 = val1.ToString();
      string sVal2 = val2.ToString();
      if (sVal1 != "2")
      {
        throw new Exception("只有已匹配的订单才可以关联");

      }
      if (sVal2 != "1")
      {
        throw new Exception("只有未匹配的PayPal订单才可以关联");
      }
      SqlParameter[] p = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@TransactionId",SqlDbType.VarChar),
        new SqlParameter("@UserId",SqlDbType.VarChar)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          p[0].Value = ebayUserId;
          p[1].Value = oid;
          p[2].Value = transactionId;
          p[3].Value = userId;

          SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_MatchRelation", p);
          try
          {
            trans.Commit();
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
    }

    public int CheckPPStatusId(string transactionId)
    {
      string cmdText = @"select StatusId from PPOrder where TransactionId =@TransactionId ";
      SqlParameter p = new SqlParameter("@TransactionId", SqlDbType.VarChar);
      p.Value = transactionId;
      try
      {
        DataTable ppdt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);
        if (ppdt.Rows.Count > 0)
        {
          string status = ppdt.Rows[0]["StatusId"].ToString();
          if (status == "1")//PPOrder状态为未匹配
          {
            return 1;
          }
        }
      }
      catch
      {
        return 0;
      }
      return 0;
    }
    /// <summary>
    /// 手动匹配
    /// </summary>
    /// <param name="oid"></param>
    /// <param name="userId"></param>
    /// <param name="transactionId"></param>
    public void HandMatch(string[] oid, string userId, string transactionId)
    {
      DateTime matchDate = DateTime.Now;
      SqlParameter[] p = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@TransactionId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@UserId",SqlDbType.VarChar),
        new SqlParameter("@MatchDate",SqlDbType.DateTime)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          for (int i = 0; i < oid.Length; i++)
          {
            string ebayUserId = oid[i];
            string orderId = oid[i + 1];
            p[0].Value = ebayUserId;
            p[1].Value = transactionId;
            p[2].Value = orderId;
            p[3].Value = userId;
            p[4].Value = matchDate;
            i++;
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_handMatch", p);
          }
          try
          {
            trans.Commit();
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
    }

    /// <summary>
    /// 客户退款
    /// </summary>
    /// <param name="oid"></param>
    /// <param name="empId"></param>
    /// <param name="Muser"></param>
    public void KH_TK(string[] oid, int empId, string Muser)
    {

      DateTime matchDate = DateTime.Now;
      SqlParameter[] p = { 
        new SqlParameter("@BillNO",SqlDbType.VarChar),
        new SqlParameter("@Amount",SqlDbType.Money),
        new SqlParameter("@Currency",SqlDbType.VarChar),
        new SqlParameter("@FDate",SqlDbType.DateTime),
        new SqlParameter("@FEmpID",SqlDbType.Int),
        new SqlParameter("@Muser",SqlDbType.VarChar)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          for (int i = 0; i < oid.Length; i++)
          {
            p[0].Value = oid[i];
            p[1].Value = oid[i + 1];
            p[2].Value = oid[i + 2];
            p[3].Value = matchDate;
            p[4].Value = empId;
            p[5].Value = Muser;
            i += 2;
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Ins_TK", p);
          }
          try
          {
            trans.Commit();
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
    }

    /// <summary>
    /// 退款单作废时，修改订单状态。2012-8-6
    /// </summary>
    /// <param name="oid"></param>
    /// <param name="Muser"></param>
    public void KH_TK_Zfei(string[] oid, string Muser)
    {
      DateTime matchDate = DateTime.Now;
      string cmdText = "Update Refund Set StatusId = 2 ,Muser = @Muser,Mtime = @d,InvalidDate = @d Where RefundId = @RefundId and EbayUserId =@EbayUserId";
      string upordercmdText = @"Update Orders set OrderStatusId =@OrderStatusId ,Muser =@Muser ,mtime = getdate()where  EbayUserId =@EbayUserId and OrderSalesRecordNumber =@OrderSalesRecordNumber";
      SqlParameter[] p = { 
        new SqlParameter("@RefundId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@d",SqlDbType.DateTime)
      };
      SqlParameter[] p2 = {                                
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderSalesRecordNumber",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@OrderStatusId",SqlDbType.Int)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          for (int i = 0; i < oid.Length; i++)
          {
            p[0].Value = oid[i];
            p[1].Value = oid[i + 1];
            p[2].Value = Muser;
            p[3].Value = matchDate;

            p2[0].Value = oid[i + 1];
            p2[1].Value = oid[i + 2];
            p2[2].Value = Muser;

            //退款类别
            int flag = 0;
            if (int.Parse(oid[i + 3]) == 1)
            {
              flag = 1;
            }
            else
            {
              flag = 2;
            }
            p2[3].Value = flag;
            i += 3;
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, upordercmdText, p2);
          }
          try
          {
            trans.Commit();
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
    }

    /// <summary>
    /// 订单信息修改
    /// </summary>
    /// <param name="buyerId">买家帐号</param>
    /// <param name="buyerName">买家名称</param>
    /// <param name="asline1">地址1</param>
    /// <param name="asline2">地址2</param>
    /// <param name="city">城市</param>
    /// <param name="county">镇</param>
    /// <param name="postalcode">邮编</param>
    /// <param name="country">国家</param>
    /// <param name="tel">电话</param>
    /// <param name="userid">付款人帐号</param>
    /// <param name="ebayuserid">Ebay帐号</param>
    /// <param name="orderid">订单号</param>
    /// <param name="DeliveryId">发货方式代号</param>
    /// <param name="ReBuilderReson">重发原因</param>
    public void UpdateOrder(string buyerId, string buyerName, string asline1, string asline2, string city, string county, string postalcode, string country, string tel, string userid, string ebayuserid, string orderid, string postageservice, string ReBuilderReson, string OrderRetentionReson, string BeiZhu)
    {
      DateTime mtime = DateTime.Now;
      string cmdText = @"Update Orders Set BuyerId = @BuyerId,BuyerName = @BuyerName,AddressLine1 = @AddressLine1,AddressLine2 = @AddressLine2,City = @City,County = @County, PostalCode = @PostalCode,Country = @Country,ContactPhoneNumber = @ContactPhoneNumber,Muser = @Muser,Mtime = @Mtime,postageservice = @postageservice,ReBuilderReson = @ReBuilderReson ,[OrderRetentionReson]=@OrderRetentionReson,[BeiZhu]=@BeiZhu Where EbayUserId = @EbayUserId And OrderId = @OrderId";
      //修改时间：2012-8-28 (UserId ,BuyerFullName)
      //string cmdText = @"Update Orders Set UserId = @BuyerId,BuyerFullName = @BuyerName,AddressLine1 = @AddressLine1,AddressLine2 = @AddressLine2,City = @City,County = @County, PostalCode = @PostalCode,Country = @Country,ContactPhoneNumber = @ContactPhoneNumber,Muser = @Muser,Mtime = @Mtime,DeliveryId = @DeliveryId,ReBuilderReson = @ReBuilderReson Where EbayUserId = @EbayUserId And OrderId = @OrderId";
      string cmdText2 = @"Update Orders Set OrderStatusId = 1,OrderRetentionReson = '',Mtime = getdate() Where EbayUserId = @EbayUserId And OrderId = @OrderId And OrderStatusId = 3 And OrderRetentionReson = '' and DeliveryId <> 0";//OrderRetentionReson = '发货方式为空'
      SqlParameter[] p = { 
        new SqlParameter("@BuyerId",SqlDbType.VarChar),
        new SqlParameter("@BuyerName",SqlDbType.NVarChar),
        new SqlParameter("@AddressLine1",SqlDbType.NVarChar),
        new SqlParameter("@AddressLine2",SqlDbType.NVarChar),
        new SqlParameter("@City",SqlDbType.NVarChar),
        new SqlParameter("@County",SqlDbType.NVarChar),
        new SqlParameter("@PostalCode",SqlDbType.NVarChar),
        new SqlParameter("@Country",SqlDbType.NVarChar),
        new SqlParameter("@ContactPhoneNumber",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@Mtime",SqlDbType.DateTime),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@postageservice",SqlDbType.NVarChar),
        new SqlParameter("@ReBuilderReson",SqlDbType.VarChar),
        new SqlParameter("@OrderRetentionReson",SqlDbType.VarChar),
        new SqlParameter("@BeiZhu",SqlDbType.VarChar)

      };
      p[0].Value = buyerId;
      p[1].Value = buyerName;
      p[2].Value = asline1;
      p[3].Value = asline2;
      p[4].Value = city;
      p[5].Value = county;
      p[6].Value = postalcode;
      p[7].Value = country;
      p[8].Value = tel;
      p[9].Value = userid;
      p[10].Value = mtime;
      p[11].Value = ebayuserid;
      p[12].Value = orderid;
      p[13].Value = postageservice;
      p[14].Value = ReBuilderReson;
      p[15].Value = OrderRetentionReson;
      p[16].Value = BeiZhu;

      SqlParameter[] p2 = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar)
      };
      p2[0].Value = ebayuserid;
      p2[1].Value = orderid;
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText2, p2);
            trans.Commit();                        
          }
          catch (Exception)
          {
            trans.Rollback();
            throw;
          }

        }
      }
    }

    /// <summary>
    /// 生成凭证
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public bool WriteVoucher(DateTime dt)
    {
      SqlParameter[] p = { 
        new SqlParameter("@FDate",SqlDbType.VarChar)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          p[0].Value = dt.ToString("yyyy-MM-dd");
          try
          {
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Ins_PZ", 240, p);
          }
          catch
          {
            throw;
          }
          try
          {
            trans.Commit();
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
      return true;
    }

    /// <summary>
    /// 获取不完全代码订单信息
    /// </summary>
    /// <param name="ebayUserId">Ebay帐号</param>
    /// <param name="OrderId">订单号</param>
    /// <returns></returns>
    public DataTable GetNoCompleteCustomLable(string ebayUserId, string OrderId)
    {
      SqlParameter[] p = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar)
      };
      p[0].Value = ebayUserId;
      p[1].Value = OrderId;
      return SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_GetNoCompleteCustomLable", p);

    }

    /// <summary>
    /// 获取不完全代码产品信息
    /// </summary>
    /// <param name="productId">产品Id</param>
    /// <param name="FStockId">仓库Id</param>
    /// <returns></returns>
    public DataTable GetProductById2(string productId)
    {
      string cmdText = @"Select 'false' as chk,p2.ProductId2,p.ProductName,p2.Qty,1 as cQty,p.UserCode,
                isnull(v_wfh.FQty,0) - ISNULL(v_wfh.sQty,0) as FQty,
                FLOOR((isnull(v_wfh.FQty,0) - ISNULL(v_wfh.sQty,0))/p2.Qty) as entyQty,
                isnull(K3_Inventory.Inventory_Price,0) as FOrderPrice,
                ISNULL(m.FStockId,0) as FStockId
                From Product2 p2 
                left Outer Join Product p On p2.ProductId2 = p.ProductId
                left outer join StockMapping m on p.DeliveryWarehouseId = m.WareHouseId
                left outer join v_wfh on p.FitemId = v_wfh.FitemId and v_wfh.FStockID = m.FStockId
                left outer join K3_Inventory on p.FitemId = K3_Inventory.FitemId and m.FStockId = K3_Inventory.StockId
                Where p2.ProductId = @ProductId
                order by FQty desc";

      SqlParameter[] p = { 
        new SqlParameter("@ProductId",SqlDbType.VarChar)
      };
      p[0].Value = productId;
      return SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);
    }

    /// <summary>
    /// 验证是否不完全代码
    /// </summary>
    /// <param name="ebayUserId"></param>
    /// <param name="OrderId"></param>
    /// <returns></returns>
    public bool CheckNoCompleteCustomLable(string ebayUserId, string OrderId)
    {
      string cmdText = "Select Count(1) From Orders Where ebayUserId = @EbayUserId And OrderId = @OrderId And Standard = '不完全代码'";
      SqlParameter[] p = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar)
      };
      p[0].Value = ebayUserId;
      p[1].Value = OrderId;
      return (int)SqlHelper.ExecuteScalar(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p) > 0;
    }

    /// <summary>
    /// 批量修改
    /// </summary>
    /// <param name="list">要修改的订单集合</param>
    /// <param name="orderStatusId">订单状态</param>
    /// <param name="deliveryId">发货方式</param>
    /// <param name="userId">用户代码</param>
    public void ReviseOrders(List<OrderInfo> list, int orderStatusId, int deliveryId, string userId)
    {
      //SqlParameter[] p ={
      //                       new SqlParameter ("@OrderStatusId",SqlDbType .Int),
      //                       new SqlParameter ("@DeliveryId",SqlDbType .Int),
      //                       new SqlParameter ("@EbayUserId",SqlDbType.VarChar),
      //                       new SqlParameter ("@OrderId",SqlDbType.VarChar),
      //                       new SqlParameter ("@Muser",SqlDbType.VarChar)
      //                   };

      //SqlParameter[] p2 ={
      //                       new SqlParameter ("@EbayUserId",SqlDbType.VarChar),
      //                       new SqlParameter ("@OrderId",SqlDbType.VarChar)
      //                   };
      //using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      //{
      //    connection.Open();
      //    using (SqlTransaction trans = connection.BeginTransaction())
      //    {
      //        try
      //        {
      //            foreach (OrderInfo m in list)
      //            {
      //                p[0].Value = orderStatusId;
      //                p[1].Value = deliveryId;
      //                p[2].Value = m.EbayUserId;
      //                p[3].Value = m.OrderId;
      //                p[4].Value = userId;

      //                p2[0].Value = m.EbayUserId;
      //                p2[1].Value = m.OrderId;
      //                SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Order_PLXG", p);
      //                SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_check_order", p2);
      //            }
      //            trans.Commit();
      //        }
      //        catch (Exception ex)
      //        {
      //            trans.Rollback();
      //            throw ex;
      //        }
      //    }
      //}
    }
    /// <summary>
    /// 获取预打印产品信息
    /// </summary>
    /// <param name="Where"></param>
    /// <param name="paralist"></param>
    /// <returns></returns>
    public DataTable GetPreProductPrint(string Where, List<SqlParameter> paralist)
    {
      string cmdText = @"Select distinct  acc.ReturnAddresser,Orders.EbayUserId,Orders.OrderId,c.ParcelId,p.UserCode + ' < ' + cast(a.Quantity as varchar) + ' > ' as pro,
                    Orders.BuyerId,Orders.AddressLine1,Orders.BuyerName,
                    Orders.AddressLine2,Orders.city,Orders.County,Orders.PostalCode,Orders.Memo,
                    Orders.Country,Orders.IsReBuilderOrder,ExpressShippingMethods.DeliveryCode,ExpressShippingMethods.PictureId
                    From CustomerResolve a 
                    Inner Join Orders  On a.OrderLineitemId = Orders.OrderLineitemId And a.EbayUserId = Orders.EbayUserId
                    Inner Join Product p On a.ProductId = p.ProductId
                    Left Outer Join ParcelOrder c on c.OrderId = Orders.OrderId and c.EbayUserId = Orders.EbayUserId
                    Left Outer Join EbayAccount acc on a.EbayUserId = acc.EbayUserId
                    Left Outer Join ExpressShippingMethods on Orders.DeliveryId = ExpressShippingMethods.Id
                    Where Orders.AddressLine1 <> '' And Orders.OrderStatusId = 2 {0}
                    Order By Orders.EbayUserId,Orders.OrderId";
      return SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, string.Format(cmdText, Where), paralist.ToArray());
    }
    /// <summary>
    /// 获取打印产品信息
    /// </summary>
    /// <param name="Where"></param>
    /// <param name="paralist"></param>
    /// <returns></returns>
    public DataTable GetProductPrint(string Where, List<SqlParameter> paralist)
    {
      string cmdText = @"select vw_Inventory.FName,Product.UserCode,b.FFullNumber,Sum(CustomerResolve.Quantity) as sQty,vw_Inventory.FQty
            from CustomerResolve left outer join Product on CustomerResolve.ProductId = Product.ProductId
            left outer join vw_Inventory on Product.FitemId = vw_Inventory.FitemId and vw_Inventory.FStockID = CustomerResolve.WarehouseId
            left outer join Orders on CustomerResolve.OrderLineItemId = Orders.OrderLineItemId and CustomerResolve.EbayUserId = Orders.EbayUserId
            left outer join AIS2011120420456.dbo.ICInventory a on a.FStockID = CustomerResolve.WarehouseId and a.FItemID = Product.FitemId
            left outer join AIS2011120420456.dbo.t_StockPlace b on a.FStockPlaceID = b.FSPID
            where  Orders.OrderStatusId  = 2 {0}
            group by Product.FitemId,Product.UserCode,vw_Inventory.FName,vw_Inventory.FQty,b.FFullNumber
            order by vw_Inventory.FName,Product.UserCode";
      return SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, string.Format(cmdText, Where), paralist.ToArray());
    }

    /// <summary>
    /// 获取审核产品信息
    /// </summary>
    /// <param name="Where"></param>
    /// <param name="paralist"></param>
    /// <returns></returns>
    /// 修改时间：2012-8-14 条件原为：Orders.Standard <> '不完全代码'
    public DataTable GetToK3Order(string Where, List<SqlParameter> paralist)
    {
      string cmdText = @"Select distinct Orders.EbayUserId + Orders.OrderId as eOrderId,
            a.OrderLineItemId,Orders.OrderId,a.Quantity,Orders.EbayUserId,
            v.Inventory_Price as SalePrice,Orders.DeliveryId,a.WarehouseId,b.FitemId,
            Orders.PayPalTransactionID as TransactionId,
            MIN(Orders.SaleDate) as SaleDate,
            MIN(p.CreateDateTime) as CreateDateTime,
            p.Currency,Orders.Standard
            from Orders
            Left Outer Join CustomerResolve a On a.OrderLineItemId = Orders. OrderLineItemId And a.EbayUserId = Orders.EbayUserId
            Left Outer Join Product b on a.ProductId = b.ProductId
            Left Outer Join PPOrder p on Orders.EbayUserId = p.EbayUserId and Orders.PayPalTransactionID = p.TransactionId
            Left Outer Join K3_Inventory v on b.FitemId = v.FItemId and a.WarehouseId = v.StockId
            where Orders.OrderStatusId = 1 And Orders.MatchStatusId = 2 {0}
            group by
            a.OrderLineItemId,Orders.OrderId,a.Quantity,Orders.EbayUserId,
            v.Inventory_Price,Orders.DeliveryId,a.WarehouseId,b.FitemId,
            Orders.PayPalTransactionID,
            p.Currency,Orders.Standard Order By EbayUserId,OrderId";
      return SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, string.Format(cmdText, Where), paralist.ToArray());
    }
    //获取审核产品信息test 修改时间：2012-10-17
    public DataTable GetToK3OrderTest(string Where, List<SqlParameter> paralist)
    {
      string cmdText = @"Select distinct Orders.EbayUserId + Orders.OrderId as eOrderId,
            a.OrderLineItemId,Orders.OrderId,a.Quantity,Orders.EbayUserId,
            v.Inventory_Price as SalePrice,Orders.DeliveryId,a.WarehouseId,b.FitemId,
            Orders.PayPalTransactionID as TransactionId,
            MIN(Orders.SaleDate) as SaleDate,
            MIN(p.CreateDateTime) as CreateDateTime,
            p.Currency,Orders.Standard,Gross
            from Orders
            Left Outer Join CustomerResolve a On a.OrderLineItemId = Orders.OrderLineItemId And a.EbayUserId = Orders.EbayUserId
            Left Outer Join Product b on a.ProductId = b.ProductId
            Left Outer Join PPOrder p on Orders.EbayUserId = p.EbayUserId and Orders.PayPalTransactionID = p.TransactionId
            Left Outer Join K3_Inventory v on b.FitemId = v.FItemId and a.WarehouseId = v.StockId
            where Orders.OrderStatusId = 1 And Orders.MatchStatusId = 2 {0}
            group by
            a.OrderLineItemId,Orders.OrderId,a.Quantity,Orders.EbayUserId,
            v.Inventory_Price,Orders.DeliveryId,a.WarehouseId,b.FitemId, 
            Orders.PayPalTransactionID,Gross,
            p.Currency,Orders.Standard Order By Orders.EbayUserId,Orders.OrderId";
      return SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, string.Format(cmdText, Where), paralist.ToArray());
    }        

    /// <summary>
    /// 订单重发
    /// </summary>
    /// <param name="OrderId">要重发的订单号</param>
    /// <param name="EbayUserId">Ebay帐号</param>
    /// <param name="buyerName">买家名称</param>
    /// <param name="addr1">地址1</param>
    /// <param name="addr2">地址2</param>
    /// <param name="deliveryId">发货方式代号</param>
    /// <param name="reson">重发原因</param>
    /// <param name="city">城市</param>
    /// <param name="county">城镇</param>
    /// <param name="postalCode">邮编</param>
    /// <param name="country">国家</param>
    /// <param name="UserId">用户代号</param>
    /// <param name="list">重发产品列表</param>
    /// <returns></returns>
    public bool ReBuilderOrder(string OrderId, string EbayUserId, string buyerName, string addr1, string addr2, string PostageService, string reson, string city, string county, string postalCode, string country, String type, string UserId, List<Model.CustomerResolve> list)
    {
      SqlParameter[] p = {
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@BuyerName",SqlDbType.NVarChar),
        new SqlParameter("@Addr1",SqlDbType.NVarChar),
        new SqlParameter("@Addr2",SqlDbType.NVarChar),
        new SqlParameter("@PostageService ",SqlDbType.NVarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@reson",SqlDbType.NVarChar),
        //新添加的字段
        new SqlParameter("@City",SqlDbType.NVarChar),
        new SqlParameter("@County",SqlDbType.NVarChar),
        new SqlParameter("@PostalCode",SqlDbType.NVarChar),
        new SqlParameter("@Country",SqlDbType.NVarChar),
        new SqlParameter("@Type", SqlDbType.VarChar),
        new SqlParameter("@Suffix",SqlDbType.VarChar,3)
      };
      SqlParameter[] p2 = { 
        new SqlParameter("@Quantity",SqlDbType.Int),
        new SqlParameter("@WarehouseId",SqlDbType.Int),
        new SqlParameter("@SalePrice",SqlDbType.Money),
        new SqlParameter("@ItemCost",SqlDbType.Money),
        new SqlParameter("@TransactionFees",SqlDbType.Money),
        new SqlParameter("@PayFee",SqlDbType.Money),
        new SqlParameter("@TheoryPostAge",SqlDbType.Money),
        new SqlParameter("@ActualPostage",SqlDbType.Money),
        new SqlParameter("@PackageFee",SqlDbType.Money),
        new SqlParameter("@RefundLoss",SqlDbType.Money),
        new SqlParameter("@Rate",SqlDbType.Money),
        new SqlParameter("@Mtime",SqlDbType.DateTime),
        new SqlParameter("@Atime",SqlDbType.DateTime),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderLineItemId",SqlDbType.VarChar),
        new SqlParameter("@ProductId",SqlDbType.VarChar),
        new SqlParameter("@CustomerLable",SqlDbType.VarChar),
        new SqlParameter("@MNote",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@Auser",SqlDbType.VarChar)                             
      };
      SqlParameter[] p3 = {
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@Suffix", SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@Type", SqlDbType.VarChar)
      };

      DateTime addTime = DateTime.Now;
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            p[0].Value = OrderId;
            p[1].Value = UserId;
            p[2].Value = buyerName;
            p[3].Value = addr1;
            p[4].Value = addr2;
            p[5].Value = PostageService;
            p[6].Value = EbayUserId;
            p[7].Value = reson;
            p[8].Value = city;
            p[9].Value = county;
            p[10].Value = postalCode;
            p[11].Value = country;
            p[12].Value = type;
            p[13].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_order_rebuilder", p);

            string Suffix = p[13].Value.ToString();

            foreach (Model.CustomerResolve m in list)
            {
              p2[0].Value = m.Quantity;
              p2[1].Value = m.WarehouseId;
              p2[2].Value = m.SalePrice;
              p2[3].Value = m.ItemCost;
              p2[4].Value = m.TransactionFees;
              p2[5].Value = m.PayFee;
              p2[6].Value = m.TheoryPostAge;
              p2[7].Value = m.ActualPostage;
              p2[8].Value = m.PackageFee;
              p2[9].Value = m.RefundLoss;
              p2[10].Value = m.Rate;
              p2[11].Value = addTime;
              p2[12].Value = "1900-1-1";
              p2[13].Value = m.EbayUserId;
              p2[14].Value = OrderId + Suffix;
              p2[15].Value = m.ProductId;
              p2[16].Value = m.CustomerLable;
              p2[17].Value = "";
              p2[18].Value = UserId;
              p2[19].Value = "";
              SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_order_rebuilder_detail", p2);
            }
            p3[0].Value = EbayUserId;
            p3[1].Value = OrderId;
            p3[2].Value = Suffix;
            p3[3].Value = UserId;
            p3[4].Value = type;
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_check_baoliu", p3);
            trans.Commit();
            return true;
          }
          catch (Exception ex)
          {
            trans.Rollback();
            FileLog.CreateLog(LogAction.Error, EbayUserId, ex.ToString());
          }
        }

      }
      return false;
    }


    /// <summary>
    /// 退款单修改
    /// </summary>
    /// <param name="ebayuserId"></param>
    /// <param name="refundId"></param>
    /// <param name="orderId"></param>
    /// <param name="totalPrice"></param>
    /// <param name="disputeId"></param>
    /// <param name="refundType"></param>
    /// <param name="departmentId"></param>
    /// <param name="userId"></param>
    /// <param name="retMsg"></param>
    /// <returns></returns>
    public bool RefundEditData(string ebayuserId, string refundId, string orderId, decimal totalPrice, int disputeId, int refundType, int departmentId, string userId, ref string retMsg)
    {
      DateTime mtime = DateTime.Now;
      string cmdText = @"Update Refund Set OrderId=@OrderId,RefundAmount=@RefundAmount,DisputeType=@DisputeType,RefundType=@RefundType,BlameDepartments=@BlameDepartments,Muser=@Muser,Mtime=@Mtime 
                                            Where EbayUserId=@EbayUserId And RefundId=@RefundId";
      SqlParameter[] p = {
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@RefundAmount",SqlDbType.Money),
        new SqlParameter("@DisputeType",SqlDbType.Int),
        new SqlParameter("@RefundType",SqlDbType.Int),
        new SqlParameter("@BlameDepartments",SqlDbType.Int),
        new SqlParameter("@Muser",SqlDbType.VarChar),
        new SqlParameter("@Mtime",SqlDbType.DateTime),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@RefundId ",SqlDbType.VarChar)
      };
      p[0].Value = orderId;
      p[1].Value = totalPrice;
      p[2].Value = disputeId;
      p[3].Value = refundType;
      p[4].Value = departmentId;
      p[5].Value = userId;
      p[6].Value = mtime;
      p[7].Value = ebayuserId;
      p[8].Value = refundId;
      try
      {
        int val = SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);
        if (val > 0)
        {
          return true;
        }
      }
      catch
      {
        retMsg = "系统错误！..";
        return false;
      }
      return false;
    }

    /// <summary>
    /// 已审核的退款单，二次退款 修改时间：2012-8-6-7
    /// </summary>
    /// <param name="ebayuserId"></param>
    /// <param name="refundId"></param>
    /// <param name="orderId"></param>
    /// <param name="totalPrice"></param>
    /// <param name="disputeId"></param>
    /// <param name="refundType"></param>
    /// <param name="departmentId"></param>
    /// <param name="userId"></param>
    /// <returns></returns>
    public bool SecondRefund(string ebayuserId, string refundId, string orderId, decimal totalPrice, int disputeId, int refundType, int departmentId, string userId)
    {
      DateTime mtime = DateTime.Now;
      string tabcmdText = @"Select TransactionId,BuyerId,BuyerFullName,Currency,AuditFlag From Refund Where EbayUserId=@EbayUserId And RefundId=@RefundId";
      SqlParameter[] ps = { 
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@RefundId",SqlDbType.VarChar)                                
      };
      ps[0].Value = ebayuserId;
      ps[1].Value = refundId;

      DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, tabcmdText, ps);
      if (table.Rows.Count == 0)
      {
        return false;
      }
      string transactionId = table.Rows[0]["TransactionId"].ToString();
      string buyerId = table.Rows[0]["BuyerId"].ToString();
      string buyerName = table.Rows[0]["BuyerFullName"].ToString();
      string Currency = table.Rows[0]["Currency"].ToString();
      int AuditFlag = int.Parse(table.Rows[0]["AuditFlag"].ToString());

      string cmdText = @"Insert Into Refund(RefundId,EbayUserId,OrderId,TransactionId,RefundAmount,Currency,StatusId,RefundType,DisputeType,BlameDepartments,RefundDate,BuyerId,BuyerFullName,InvalidDate,Mnote,Muser,Mtime,Auser,Atime,AuditFlag)
               Values (@RefundId,@EbayUserId,@OrderId,@TransactionId,@TotalPrice,@Currency,0,@RefundType,@DisputeType,@BlameDepartments,@RefundDate ,@BuyerId,@BuyerFullName,'1900-1-1','',@UserId,@Mtime,'','1900-1-1',@AuditFlag)";
      string cmdText2 = @"Update Refund  set StatusId=4 ,Mtime=@Mtime where EbayUserId =@EbayUserId and RefundId=@RefundId";

      SqlParameter[] p = {
        new SqlParameter("@RefundId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@TransactionId",SqlDbType.VarChar),
        new SqlParameter("@TotalPrice",SqlDbType.Money),
        new SqlParameter("@Currency",SqlDbType.VarChar),
        new SqlParameter("@RefundType",SqlDbType.Int),
        new SqlParameter("@DisputeType",SqlDbType.Int),
        new SqlParameter("@BlameDepartments",SqlDbType.Int),
        new SqlParameter("@RefundDate",SqlDbType.DateTime),
        new SqlParameter("@BuyerId",SqlDbType.VarChar),
        new SqlParameter("@BuyerFullName",SqlDbType.VarChar),
        new SqlParameter("@UserId",SqlDbType.VarChar),
        new SqlParameter("@Mtime",SqlDbType.DateTime),
        new SqlParameter("@AuditFlag",SqlDbType.Int)
      };
      SqlParameter[] p2 = {
        new SqlParameter("@RefundId",SqlDbType.VarChar),
        new SqlParameter("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter("@Mtime",SqlDbType.DateTime)
      };
      p[0].Value = refundId + "_1";
      p[1].Value = ebayuserId;
      p[2].Value = orderId;
      p[3].Value = transactionId;
      p[4].Value = totalPrice;
      p[5].Value = Currency;
      p[6].Value = refundType;
      p[7].Value = disputeId;
      p[8].Value = departmentId;
      p[9].Value = mtime;
      p[10].Value = buyerId;
      p[11].Value = buyerName;
      p[12].Value = userId;
      p[13].Value = mtime;
      p[14].Value = AuditFlag;

      p2[0].Value = refundId;
      p2[1].Value = ebayuserId;
      p2[2].Value = mtime;
      try
      {
        int val = 0;
        using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
        {
          connection.Open();
          using (SqlTransaction trans = connection.BeginTransaction())
          {
            val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
            val += SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText2, p2);
            try
            {
              trans.Commit();
            }
            catch
            {
              trans.Rollback();
              return false;
            }
          }
        }
        if (val > 0)
        {
          return true;
        }
      }
      catch
      {
        return false;
      }
      return false;
    }

    /// <summary>
    /// 不完全代码随机最优分配
    /// </summary>
    public void RadomProcessesNoComplete(string QueryDate, string Muser, ref string InfoMsg)
    {
      DataTable tableOrder = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_GetNoCompleteAll", new SqlParameter("@Date", QueryDate));
      if (tableOrder.Rows.Count == 0)
      {
        InfoMsg += "<li>没有要处理的订单</li>";
        return;
      }

      int count = 0;
      int failCount = 0;

      string old_ooId = "";
      string ooId = "";
      DataRow[] allRow = tableOrder.Select("", "ooId");
      foreach (DataRow row in allRow)
      {
        ooId = row["ooId"].ToString();
        if (ooId == old_ooId)
        {
          continue;
        }

        old_ooId = ooId;

        //客户下单数量
        int orderQty = Convert.ToInt32(row["oQty"]);

        //解析后的数量
        int aQty = Convert.ToInt32(row["aQty"]);

        string SKU = row["UserCode"].ToString();

        string SalesRecordNumber = row["SalesRecordNumber"].ToString();

        string customLable = row["CustomLable"].ToString();

        string ebayUserId = row["EbayUserId"].ToString();

        string orderLineItemId = row["OrderLineItemId"].ToString();

        string orderId = row["OrderId"].ToString();

        string cmdText = @"select p.ProductId2,p.Qty,
                    FLOOR((isnull(h.FQty,0) - ISNULL(h.sQty,0))/(p.Qty)) as entyQty,
                    isnull(K3_Inventory.Inventory_Price,0) as FOrderPrice,isnull(m.FStockId,0) as FStockId from Product2 p
                    left outer join Product p0 on p.ProductId2 = p0.ProductId
                    left outer join StockMapping m on p0.DeliveryWarehouseId = m.WareHouseId
                    left outer join v_wfh h on p0.FitemId = h.FitemId
                    left outer join K3_Inventory on p0.FitemId = K3_Inventory.FitemId and m.FStockId = K3_Inventory.StockId
                    where p.ProductId = @SKU
                    and h.FStockID = m.FStockId and FLOOR((isnull(h.FQty,0) - ISNULL(h.sQty,0))/(p.Qty)) > 0
                    order by FLOOR((isnull(h.FQty,0) - ISNULL(h.sQty,0))/(p.Qty)) desc";

        SqlParameter[] p = { 
          new SqlParameter("@SKU",SKU),
        };
        p[0].Value = SKU;

        DataTable stockProduct = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);

        if (stockProduct.Rows.Count == 0)
        {
          failCount++;
          InfoMsg += "<li>订单【" + SalesRecordNumber + "】库存不够,不能处理</li>";
          continue;
        }
        object objTotalQty = stockProduct.Compute("Sum(entyQty)", "");
        int totalQty = objTotalQty == DBNull.Value ? 0 : Convert.ToInt32(objTotalQty);
        if (totalQty < aQty)
        {
          failCount++;
          InfoMsg += "<li>订单【" + SalesRecordNumber + "】库存不够,不能处理</li>";
          continue;
        }

        List<ebay.Model.CustomerResolve> list = new List<Model.CustomerResolve>();
        if (aQty == 1)
        {
          ebay.Model.CustomerResolve m = new ebay.Model.CustomerResolve();

          DataRow rowItem = stockProduct.Select("", "entyQty Desc")[0];

          decimal orderPrice = Convert.ToDecimal(rowItem["FOrderPrice"]);
          string productId = rowItem["ProductId2"].ToString();
          int stockId = Convert.ToInt32(rowItem["FStockId"]);
          int fQty = Convert.ToInt32(rowItem["Qty"]);
          m.ActualPostage = 0m;
          m.Atime = new DateTime(1900, 1, 1);
          m.Auser = "";
          m.CustomerLable = customLable;
          m.EbayUserId = ebayUserId;
          m.ItemCost = orderPrice;
          m.MNote = "";
          m.Mtime = DateTime.Now;
          m.Muser = Muser;
          m.OrderLineItemId = orderLineItemId;
          m.PackageFee = 0m;
          m.PayFee = 0m;
          m.ProductId = productId;
          m.Quantity = aQty * fQty;
          m.Rate = 1m;
          m.RefundLoss = 0m;
          m.SalePrice = orderPrice;
          m.TheoryPostAge = 0m;
          m.TransactionFees = 0m;
          m.WarehouseId = stockId;
          list.Add(m);
          try
          {
            ebay.Dal.CustomerResolve c = new CustomerResolve();
            c.AddList(list, ebayUserId, orderId);
            InfoMsg += "<li>订单【" + SalesRecordNumber + "】处理成功</li>";
            count++;
          }
          catch (Exception)
          {
            InfoMsg += "<li>订单【" + SalesRecordNumber + "】存档失败</li>";
            failCount++;
          }
          continue;
        }
        else
        {
          //属于未处理的总数
          int RemainderTotalQty = aQty;

          foreach (DataRow rowItem in stockProduct.Rows)
          {
            if (RemainderTotalQty <= 0)
              break;
            //库存数
            int ItemStockQty = Convert.ToInt32(rowItem["entyQty"]);

            //按百分比计算分的的数量  (库存数/总库存数)*总发货数量 = 单个产品需要发货的数量
            decimal dsendQty = ((decimal)ItemStockQty / totalQty) * aQty;
            int sendQty = (int)Math.Ceiling(dsendQty);
            if (sendQty > ItemStockQty)
            {
              sendQty = ItemStockQty;
            }

            //加入此判断用于防止四舍五入多发的数量
            if (sendQty > RemainderTotalQty)
            {
              sendQty = RemainderTotalQty;
            }

            //减去此次发货数量
            RemainderTotalQty -= sendQty;

            decimal orderPrice = Convert.ToDecimal(rowItem["FOrderPrice"]);
            string productId = rowItem["ProductId2"].ToString();
            int stockId = Convert.ToInt32(rowItem["FStockId"]);
            int fQty = Convert.ToInt32(rowItem["Qty"]);
            ebay.Model.CustomerResolve m = new ebay.Model.CustomerResolve();
            m.ActualPostage = 0m;
            m.Atime = new DateTime(1900, 1, 1);
            m.Auser = "";
            m.CustomerLable = customLable;
            m.EbayUserId = ebayUserId;
            m.ItemCost = orderPrice;
            m.MNote = "";
            m.Mtime = DateTime.Now;
            m.Muser = Muser;
            m.OrderLineItemId = orderLineItemId;
            m.PackageFee = 0m;
            m.PayFee = 0m;
            m.ProductId = productId;
            m.Quantity = sendQty * fQty;
            m.Rate = 1m;
            m.RefundLoss = 0m;
            m.SalePrice = orderPrice;
            m.TheoryPostAge = 0m;
            m.TransactionFees = 0m;
            m.WarehouseId = stockId;
            list.Add(m);
          }
          //判断是否全部分配完毕,分配完毕应该等于0
          if (RemainderTotalQty > 0)
          {
            failCount++;
            InfoMsg += "<li>订单【" + SalesRecordNumber + "】系统计算有误,可能是算法问题,请咨询系统管理员</li>";
            continue;
          }
          else
          {
            try
            {
              ebay.Dal.CustomerResolve c = new CustomerResolve();
              c.AddList(list, ebayUserId, orderId);
              InfoMsg += "<li>订单【" + SalesRecordNumber + "】处理成功</li>";
              count++;
            }
            catch (Exception)
            {
              InfoMsg += "<li>订单【" + SalesRecordNumber + "】存档失败</li>";
              failCount++;
            }
            continue;
          }

        }

      }
      InfoMsg += "<li>本次操作成功【" + count + "】条。失败【" + failCount + "】条。</li>";
    }
    /// <summary>
    /// 手动保留订单
    /// </summary>
    /// <param name="list"></param>
    /// <param name="reason"></param>
    /// <param name="userId"></param>
    /// <returns></returns>
    public bool Shdbliu(List<OrderInfo> list, string reason, string userId)
    {
      string cmdText = @"Update Orders set OrderStatusId=3,OrderRetentionReson =@OrderRetentionReson,Muser=@Muser, mtime = getdate() where EbayUserId=@EbayUserId and OrderId =@OrderId ";
      SqlParameter[] p ={                                  
        new SqlParameter ("@EbayUserId",SqlDbType.VarChar),
        new SqlParameter ("@OrderId",SqlDbType.VarChar),
        new SqlParameter ("@Muser",SqlDbType.VarChar),
        new SqlParameter ("@OrderRetentionReson",SqlDbType.VarChar)
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            foreach (OrderInfo m in list)
            {
              p[0].Value = m.EbayUserId;
              p[1].Value = m.OrderId;
              p[2].Value = userId;
              p[3].Value = reason;
              SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
            }
            trans.Commit();
          }
          catch (Exception)
          {
            trans.Rollback();
            return false;
          }
        }
      }
      return true;
    }

    /// <summary>
    /// 检查缺货保留状态订单是否可以发货
    /// </summary>
    /// <param name="Msg"></param>
    public void ResetReserve(String userId, out string Msg)
    {
      //Msg = "";
      //int successCount = 0;
      //int fCount = 0;
      //DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_get_baoliu_all", null);
      ////try
      ////{
      ////    SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_reset_delivery", null);
      ////}
      ////catch
      ////{
      ////    Msg += "重置发货方式失败!\\r\\n";
      ////}
      //Dictionary<string, int> stock = new Dictionary<string, int>();
      //foreach (DataRow row in table.Rows)
      //{
      //    string productId = row["ProductId"].ToString();
      //    string whId = row["WarehouseId"].ToString();
      //    int Qty = Convert.ToInt32(row["stockQty"]);
      //    string key = productId + '-' + whId;

      //    if (stock.ContainsKey(key))
      //    {
      //        continue;
      //    }
      //    stock.Add(key, Qty);
      //}
      //foreach (DataRow row in table.Rows)
      //{
      //    string productId = row["ProductId"].ToString();
      //    string olid = row["olid"].ToString();
      //    string ebId = row["ebId"].ToString();
      //    string oid = row["oid"].ToString();
      //    string whId = row["WarehouseId"].ToString();
      //    int Quantity = Convert.ToInt32(row["Quantity"]);
      //    string key = productId + '-' + whId;
      //    int sQty = stock[key];
      //    if (sQty >= Quantity)
      //    {
      //        SqlParameter[] p = {
      //                           new SqlParameter("@EbayUserId",SqlDbType.VarChar),
      //                           new SqlParameter("@OrderLineItemId",SqlDbType.VarChar),
      //                           new SqlParameter("@OrderId",SqlDbType.VarChar)
      //                           };
      //        p[0].Value = ebId;
      //        p[1].Value = olid;
      //        p[2].Value = oid;
      //        try
      //        {
      //            SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_reset_baoliu", p);
      //            stock[key] -= Quantity;
      //            successCount++;
      //        }
      //        catch
      //        {
      //            fCount++;
      //            continue;

      //        }
      //    }
      //}
      Msg = String.Empty;
      SqlParameter[] param = {
        new SqlParameter("@MUser", SqlDbType.NVarChar),
        new SqlParameter("@Reserve", SqlDbType.Int),
        new SqlParameter("@Unaudit", SqlDbType.Int)
      };
      param[0].Value = userId;
      param[1].Direction = ParameterDirection.Output;
      param[2].Direction = ParameterDirection.Output;

      try
      {
        SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_reset_baoliu", param);
      }
      catch
      {

      }

      Msg += "本次操作 保留【" + (param[1].Value == DBNull.Value ? 0 : param[1].Value) + "】条。未审核【" + (param[2].Value == DBNull.Value ? 0 : param[2].Value) + "】条。";
    }

    /// <summary>
    ///  PayPal 手工取消
    /// </summary>     
    /// <param name="pids"></param>
    /// <param name="userId"></param>
    public void UpdateMatchFlag(string[] pids)
    {
      string cmdText = @"Update PPOrder set StatusId = 3 where TransactionId = @TransactionId and StatusId = 1";
      string cmdText2 = @"Update PPOrder set StatusId = 1 where TransactionId = @TransactionId and StatusId = 3";
      SqlParameter[] p = {                                                                                         
        new SqlParameter("@TransactionId",SqlDbType.VarChar)                    
      };
      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {

          for (int i = 0; i < pids.Length; i++)
          {
            string pid = pids[i];
            string statusId = pids[i + 1];
            p[0].Value = pid;
            i++;
            if (statusId == "1")
            {
              SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText, p);
            }
            else if (statusId == "3")
            {
              SqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText2, p);
            }
            else
            {
              throw new InvalidExpressionException();
            }
          }
          try
          {
            trans.Commit();
          }
          catch
          {
            trans.Rollback();
          }
        }
      }
    }

    public void PreAudit(string Muser)
    {
      SqlParameter[] parameters = new SqlParameter[]{
        new SqlParameter() { ParameterName = "@Muser", SqlDbType = SqlDbType.NVarChar, Size = 30, Value = Muser}
      };
      SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_PrepareAllOrders", 120, parameters);
    }

    public void PreAudit(String eBayUserId, String OrderId, String Muser)
    {
      SqlParameter[] parameters = new SqlParameter[]{
        new SqlParameter() { ParameterName = "@eBayUserId", SqlDbType = SqlDbType.NVarChar, Size = 80, Value = eBayUserId },
        new SqlParameter() { ParameterName = "@OrderId", SqlDbType = SqlDbType.NVarChar, Size = 30, Value = OrderId },
        new SqlParameter() { ParameterName = "@Muser", SqlDbType = SqlDbType.NVarChar, Size = 30, Value = Muser }
      };
      SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_PrepareOrder", parameters);
    }

    public void CancelPreAudit(String eBayUserId, String OrderId, String Muser)
    {
      SqlParameter[] parameters = new SqlParameter[]{
        new SqlParameter() { ParameterName = "@eBayUserId", SqlDbType = SqlDbType.NVarChar, Size = 80, Value = eBayUserId },
        new SqlParameter() { ParameterName = "@OrderId", SqlDbType = SqlDbType.NVarChar, Size = 30, Value = OrderId },
        new SqlParameter() { ParameterName = "@Muser", SqlDbType = SqlDbType.NVarChar, Size = 30, Value = Muser }
      };
      SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_CancelPrepareOrder", parameters);
    }

    public void Audit(String Auser, out int totalCount)
    {
      totalCount = 0;

      SqlParameter[] parameters = new SqlParameter[] {
        new SqlParameter() { ParameterName = "@Auser", SqlDbType = SqlDbType.NVarChar, Size = 30, Value = Auser },
        new SqlParameter() { ParameterName = "@OrderCount", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output }
      };

      using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
      {
        connection.Open();
        using (SqlTransaction trans = connection.BeginTransaction())
        {
          try
          {
            SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_AuditOrder", 1800, parameters);
            totalCount = Convert.ToInt32(parameters[1].Value);
            trans.Commit();
          }
          catch(Exception ex)
          {
            Console.WriteLine(ex.Message);
            trans.Rollback();
            totalCount = 0;
          }
        }
      }
    }

    public int ReplacePayPalEbayUserId(DateTime ImportDate) //PPorder帐号替换。
    {
      string cmdText = @"Update PPOrder set EbayUserId=l.eebayId 
                                    From (
	                                    select p.EbayUserId as pebayId,e.EbayUserId as eebayId,p.TransactionId from PPOrder p
	                                    inner join EbaySelling e on p.ItemID = e.ItemId and p.BuyerID = e.UserId
	                                    where p.EbayUserId <> e.EbayUserId and p.ItemID <> ''
	                                    and CONVERT(varchar(8),p.ImportDate,112) = @ImportDate
                                    ) as l
                              Where l.TransactionId =PPOrder.TransactionId and l.pebayId=PPOrder.EbayUserId";

      SqlParameter p = new SqlParameter("@ImportDate", SqlDbType.VarChar);
      p.Value = ImportDate.ToString("yyyyMMdd");
      try
      {
        int val = SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);
        if (val > 0)
        {
          return 1;
        }
      }
      catch
      {
        return 0;
      }
      return 0;
    }

    // 验证是否可以清楚标准化
    public DataTable CheckStandard(string ebayuserId, string orderId, string orderlineitemid, string type)
    {
      DataTable dt = new DataTable();
      string cmdText = @"Select Standard ,Memo From Orders Where EbayUserId=@EbayUserId and OrderId=@OrderId ";
      SqlParameter[] p = {
        new SqlParameter("@EbayUserId",SqlDbType.VarChar), 
        new SqlParameter("@OrderId",SqlDbType.VarChar)                                               
      };
      p[0].Value = ebayuserId;
      p[1].Value = orderId;

      string cmdText2 = @"Select Standard ,Memo From Orders Where EbayUserId=@EbayUserId and OrderId=@OrderId and OrderLineItemId =@OrderLineItemId";
      SqlParameter[] p2 = {
        new SqlParameter("@EbayUserId",SqlDbType.VarChar), 
        new SqlParameter("@OrderId",SqlDbType.VarChar), 
        new SqlParameter("@OrderLineItemId",SqlDbType.VarChar)                                            
      };
      p2[0].Value = ebayuserId;
      p2[1].Value = orderId;
      p2[2].Value = orderlineitemid;
      try
      {
        if (type == "1")
        {
          dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);
        }
        else
        {
          dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText2, p2);
        }
      }
      catch
      {
        dt = null;
      }
      return dt;
    }

    // 清楚标准化标记
    public int ClearStandard(string ebayuserId, string orderId, string orderlineitemid, string userId, string type)
    {
      string cmdText = @"Update Orders set Standard ='',Mnote='手工清除标准化',Muser=@Muser where EbayUserId=@EbayUserId and OrderId=@OrderId ";
      SqlParameter[] p = {
        new SqlParameter("@EbayUserId",SqlDbType.VarChar), 
        new SqlParameter("@OrderId",SqlDbType.VarChar),
        new SqlParameter("@Muser",SqlDbType.VarChar) 
      };
      p[0].Value = ebayuserId;
      p[1].Value = orderId;
      p[2].Value = userId;

      string cmdText2 = @"Update Orders set Standard ='',Mnote='手工清除标准化',Muser=@Muser where EbayUserId=@EbayUserId and OrderId=@OrderId and OrderLineItemId =@OrderLineItemId";
      SqlParameter[] p2 = {
        new SqlParameter("@EbayUserId",SqlDbType.VarChar), 
        new SqlParameter("@OrderId",SqlDbType.VarChar), 
        new SqlParameter("@OrderLineItemId",SqlDbType.VarChar), 
        new SqlParameter("@Muser",SqlDbType.VarChar) 
      };
      p2[0].Value = ebayuserId;
      p2[1].Value = orderId;
      p2[2].Value = orderlineitemid;
      p2[3].Value = userId;

      //String sqlString = @"exec sp_SingleMatchCheck @OrderID";
      SqlParameter[] parameters = new SqlParameter[] {
        new SqlParameter() { ParameterName = "@OrderID", SqlDbType = SqlDbType.VarChar, Size = 30, Value = orderId }
      };

      try
      {
        int val = 0;
        if (type == "1")
        {
          val = SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);
        }
        else
        {
          val = SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText2, p2);
        }

        if (val == 0)
          val = SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_SingleMatchCheck", parameters);

        if (val > 0)
        {
          return 1;
        }
      }
      catch
      {
        return 0;
      }
      return 0;
    }

    //客服审核检查库存
    public int CheckInventory(int fItemId, int fstockId, int Quantity, string ebayUserId, string orderId)
    {
      string cmdText = @"select sQty,FQty from v_wfh where v_wfh.FitemId=@FitemId and v_wfh.FStockID=@FStockID ";
      SqlParameter[] p = {                                                                                                                                                   
        new SqlParameter("@FitemId",SqlDbType.Int),
        new SqlParameter("@FStockID",SqlDbType.Int)
      };
      p[0].Value = fItemId;
      p[1].Value = fstockId;
      string cmdText2 = @"Update Orders Set OrderStatusId =3,OrderRetentionReson='缺货保留',Mtime=GETDATE() where EbayUserId=@EbayUserId and OrderId=@OrderId";
      SqlParameter[] p2 = {                                                                                                                                                   
        new SqlParameter("@EbayUserId",SqlDbType.VarChar ),
        new SqlParameter("@OrderId",SqlDbType.VarChar)
      };
      p2[0].Value = ebayUserId;
      p2[1].Value = orderId;
      try
      {
        DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p);
        if (dt.Rows.Count == 0)
        {
          SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, p2);
          return 0;
        }
        else
        {
          float sqty = float.Parse(dt.Rows[0]["sQty"].ToString());
          float fqty = float.Parse(dt.Rows[0]["FQty"].ToString());//Fqty为及时库存（K3库存-没有日结的零售单库存）
          if ( sqty>fqty)
          {
            SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText2, p2);
            return 0;
          }
          else
          {
            return 1;
          }
        }
      }
      catch (Exception e)
      {
        throw (e);
      }
    }

    // 批量审核检查库存
    public DataTable MoreAuditCheckInventory()
    {
      string cmdText = @" Select sQty,FQty,ProductId,FStockID,FitemId from v_wfh where  sQty > FQty and sQty<>0";
      try            
      {
        DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText, null);
        return dt;
      }
      catch
      {
        return  null;
      }
    }        

        
    /// <summary>
    /// 保留处理--订单库存不足
    /// </summary>
    /// <param name="fstockId"></param>
    /// <param name="productId"></param>
    /// <param name="fqty">库存数量</param>
    /// <param name="shsqty">“未审核”订单锁定的商品数量</param>
    /// <returns></returns>
    public int BaoLiuCheckInventory( string productId, float fqty,int sqty)   
    {
      //获取包含库存不足的订单
      string cmdText = @"SELECT  a.ProductId, SUM(a.Quantity) AS Qty, b.OrderId,b.OrderStatusId
                                    FROM dbo.CustomerResolve AS a INNER JOIN  dbo.Orders AS b ON a.OrderLineItemId = b.OrderLineItemId					
                                    WHERE(b.IsPay = 'Y') AND b.OrderStatusId in (1,3) and b.OrderRetentionReson<>'缺货保留'
                                    and a.ProductId=@ProductId
                                 GROUP BY a.ProductId,b.OrderId,b.OrderStatusId
                                 Order by b.OrderStatusId desc";
      SqlParameter[] p = {                                 
        new SqlParameter("@ProductId",SqlDbType.VarChar)
      };           
      p[0].Value = productId;

      //对未审核订单进行保留处理
      string cmdText2 = @"Update Orders Set OrderStatusId =3, OrderRetentionReson ='缺货保留',Mtime=GETDATE()
                                       Where  OrderId=@OrderId and (OrderStatusId=1 or OrderStatusId=3)";
      SqlParameter[] p2 = {                                  
        new SqlParameter("@OrderId",SqlDbType.VarChar)
      };      
      try
      {
        DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn,CommandType.Text,cmdText, p);
        if (dt.Rows.Count > 0)//处理保留订单
        {
          int val = 0;
          int baolqty = 0;
          if (sqty > fqty && fqty > 0)//计算保留商品数量
          {
            baolqty = sqty - int.Parse(fqty.ToString()); 
          }
          foreach (DataRow r in dt.Rows)
          {                       
            string oid = r["OrderId"].ToString();
            int qty = int.Parse(r["Qty"].ToString());
            p2[0].Value = oid;
            if (fqty <= 0)//库存为零
            {
              val += SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText2, p2);
            }
            else if (sqty > fqty && fqty > 0) //库存不足
            {
              baolqty = baolqty - qty;
              if (baolqty > 0)
              {
                val += SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText2, p2);
                continue;
              }
              else
              {
                val += SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, cmdText2, p2);
                return 1;
              }
            }
          }
          if (val > 0)
          {
            return 1;
          }
        }
        else //无订单需要保留
        {
          return 1;
        }
      }
      catch(Exception e)
      {
        throw (e);
      }
      return 0;
    }

    public DataTable GetOrderSummary(String eBayUserID, String orderID) {
      String sqlString = @"SELECT o.OrderID,o.UserID,s.StatusName,OrderStatusID,CASE WHEN o.ReBuilderReson='' THEN '否' ELSE '是' END AS 'Refund',o.PostageService,
CAST(o.PostageAndPackaging AS DECIMAL(10,2)) AS PostageAndPackaging,o.SaleDate,o.TotalPrice,o.PaymentMethod
FROM Orders o JOIN OrderStatus s ON o.OrderStatusID=s.StatusID WHERE eBayUserID=@eBayUserID AND OrderID=@OrderID";
      SqlParameter[] parameters = new SqlParameter[] {
        new SqlParameter() { ParameterName = "@eBayUserID", SqlDbType = SqlDbType.NVarChar, Size = 80, Value = eBayUserID },
        new SqlParameter() { ParameterName = "@OrderID", SqlDbType = SqlDbType.NVarChar, Size = 30, Value = orderID }
      };
      
      DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, sqlString, parameters);

      return table;
    }

    public DataTable GetPayPalAddress(String eBayUserID, String orderID) {
      String sqlString = @"SELECT BuyerName,AddressLine1 + ' ' + AddressLine2 AS BuyerAddress,City AS BuyerCity,Country AS BuyerCountry,PostalCode AS BuyerZipCode,BuyerEmail FROM Orders WHERE eBayUserID=@eBayUserID AND OrderID=@OrderID";
      SqlParameter[] parameters = new SqlParameter[] {
        new SqlParameter() { ParameterName = "@eBayUserID", SqlDbType = SqlDbType.NVarChar, Size = 80, Value = eBayUserID },
        new SqlParameter() { ParameterName = "@OrderID", SqlDbType = SqlDbType.NVarChar, Size = 30, Value = orderID }
      };

      return SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, sqlString, parameters);
    }
  }
}
